October 6, 2004 at 6:17 am
Hi,
I am having a problem where in i want to concatenate the values of a field across multiple rows for a given set of primary keys. To illustrate, consider the table #TEMP:
EmpNo Dept Tech ConcatField
1 CS vb
2 ecom java
1 ecom java
1 Cs pb
2 ecom asp
3 mf cobol
1 cs oracle
here for a combination of empno n dept fields i want a concatenated string which will have the TECH field values separated by a comma.
so now the concat field should be :
EmpNo Dept Tech ConcatField
1 CS vb vb,pb,oracle
2 ecom java java,asp
1 ecom java java
1 Cs pb vb,pb,oracle
2 ecom asp java,asp
3 mf cobol cobol
1 cs oracle vb,pb,oracle
I am advised not to use cursors.
Looking out for a solution.
Thanks,
Sairam
October 6, 2004 at 7:04 am
this is not the best way to do it but that would work :
CREATE FUNCTION [dbo].[fnGetEmpLanguages] (@EmpNo as int)
RETURNS varchar(4000) AS --can be more than that but the total row size cannot exceed 8060 (or 8076???) characters
BEGIN
Declare @Return as varchar(4000)
Declare @Length as smallint
set @Return = ''
Select @Return = @Return + Tech + ', ' from TEMP where EmpNo = @EmpNo
set @Length = len(@Return)
if @Length > 0
begin
--remove the trailing space and comma
set @Return = left(@Return, @Length - 2)
end
Return @Return
END
P.S. a function cannot access a temp table so you'd have to use a permanent table to use this function like so :
Select EmpNo, Dept, Tech, dbo.fnGetEmpLanguages(EmpNo) as ConcatField from dbo.YourPermTable
On the other hand in situations like these I usually do something like this :
Select EmpNo, Dept, Tech from dbo.MyTable order by EmpNo, Dept, Tech
Then I just use 2-3 while loops to run through the data and display it as needed. That way the query is much more efficient and there's little work left to do on the client side.
October 6, 2004 at 7:33 am
Without using a function but using a while loop
make sure ConCatField is NULL before running this
DECLARE @upd int
SET @upd = 1
WHILE (@upd > 0)
BEGIN
UPDATE t
SET t.ConCatField = COALESCE(t.ConCatField + ',' + x.Tech,x.Tech)
FROM #temp t
INNER JOIN #temp x
ON x.EmpNo = t.EmpNo
AND x.Dept = t.Dept
AND ISNULL(CHARINDEX(x.Tech,t.ConCatField),0) = 0
SET @upd = @@ROWCOUNT
END
Far away is close at hand in the images of elsewhere.
Anon.
October 7, 2004 at 7:01 am
With all due respect to Mr. Celko, whose background I am familiar with and grasp of SQL internals is probably beyond compare, I felt your response was a little condescending and not very helpful. Indeed good advice when an application with a front end was involved, but Mr. Gopal K. may be developing a pure data conversion tool for two disparate applications and data sources as I have done many times over the years using both suggested methods. In such a case your opinion on which of the two options, or an alternative, is more efficient would have been of more help - to supplement your other comments of course.
maddog
October 7, 2004 at 1:55 pm
This seems a little more elegant, though I don't exactly know why it works. I haven't converted it to what you want but you can do that easy enough. The function is then included in a select statement similar to the comment above
CREATE FUNCTION dbo.fWorkFuncByWorkOrder
(@WorkOrder char(11))
RETURNS varchar(500)
AS
BEGIN
DECLARE @WorkFuncs varchar(500)
SELECT @WorkFuncs = COALESCE(@WorkFuncs, '') + RTRIM(strWorkFuncCode)+ ', '
FROM tblWorkOrderDetail
WHERE strWorkOrderNo = @WorkOrder
SET @WorkFuncs=LEFT(@WorkFuncs, LEN(@WorkFuncs)-1)
RETURN @WorkFuncs
END
As for Mr. Celko's comment, we have had customers who want flat files with the information formatted in this fashion. I am as much a stickler for the proper use of the normal forms as anyone, but I'm not going to turn down business just because my customers are not as particular as me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply