Coalesce multiple rows into a comma-delimited value during Update

Question

question

graham.reeds asked Nov 5, '09

Coalesce multiple rows into a comma-delimited value during Update

I have a temporary table with a field called Method, thus:

DECLARE @CaseSites TABLE (
 BriefID  int,
 Method  varchar(60)
 -- other fields
)

Method will be filled from several rows in another table - CaseEventTypeList.

Running

SELECT * FROM CaseEventTypeList WHERE RefID = 1

Gives

RefID TypeID
1  2
1  3
1  6

Turning this into a single comma delimited result is fairly trivial:

DECLARE @CETList varchar(30)

SELECT @CETList = COALESCE(@CETList + ',', '') + CAST(CETL.[TypeID] AS varchar)
FROM CaseEventTypeList CETL 
WHERE CETL.RefID = 1

PRINT @CETList

Giving:

2,3,6

Now I need to expand this to take in the entire table. This is what I came up with:

UPDATE @CaseSites SET Method = COALESCE(Method + ',','') + CAST(CETL.TypeID AS VARCHAR)
 FROM CaseEvents CE
   JOIN CaseEventTypeList AS CETL ON CETL.RefID = CE.TypeListID
 WHERE BriefID = CE.CaseID

However this only fills Method with the first value from each set of values.

I looked online and found this but would rather not use a udf - especially when the solution feels so close.

UPDATE: The data is fairly simple, the RefId is incremented for each case, the TypeID can be any number, though only 1 to 8 are modelled currently. Thus you might have:

RefID TypeID
12  2
12  7
13  1
14  1
14  3
14  6

And this will hopefully be modelled as

SELECT Method from @CaseSites
Method 
...
12  2,7
13  1
14  1,3,6
...

t-sql sql-server-2000 concatenation

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Nov 05, 2009 at 02:44 PM

TimothyAWiseman : why remove the SQL2000 tag?

0 ·

Kev Riley ♦♦ commented · Nov 05, 2009 at 08:13 PM

I've added the tag back. No reason to remove it.

0 ·

TimothyAWiseman commented · Nov 06, 2009 at 02:08 AM

Because I goofed. I meant to add the other tags, not remove the original.

0 ·

Answer 1 · 2009-11-05T14:09:47Z

Kev Riley answered Nov 5, '09

DECLARE @CaseEventTypeList TABLE (
 RefID  int,
 TypeID int
 )


insert into @CaseEventTypeList (RefID, TypeID)
select 12,  2
union select 12,  7
union select 13,  1
union select 14,  1
union select 14,  3
union select 14,  6


SELECT distinct
	refID,
	stuff ( ( SELECT
			  ', ' + cast(TypeID  as varchar)
		  FROM
			  @CaseEventTypeList t1
		  where t1.refID = t2.refID
	FOR XML PATH ( '' ) ) , 1 , 2 , '' )
from @CaseEventTypeList t2

use this to update the @CaseSites table

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Oct 01, 2010 at 07:23 AM

Cool. This has just saved me a headache.

0 ·

Answer 2 · 2009-11-05T13:36:07Z

Christopher Klein answered Nov 5, '09

workable? Think it does what you want, just replace table names as necessary.


DECLARE @Sample TABLE (RefID INT, TypeID INT)
INSERT @Sample
SELECT 100, 1 UNION ALL
SELECT 100, 4 UNION ALL
SELECT 101, 2 UNION ALL
SELECT 101, 4 UNION ALL
SELECT 100, 5 UNION ALL
SELECT 100, 99

DECLARE @CaseSites TABLE(Method VARCHAR(60))

INSERT @CaseSites 
SELECT DISTINCT
CAST(RefID AS VARCHAR) +' '+
CASE
WHEN LEN(TypeID) > 0 THEN LEFT(TypeID, LEN(TypeID) -1)
ELSE ''
END AS TypeID
FROM (
SELECT PM.RefID as RefID
,(
SELECT CAST(P.TypeID AS VARCHAR) + ','
FROM @Sample AS P
WHERE P.RefID = PM.RefID
FOR XML PATH('')
) AS TypeID
FROM @Sample AS PM
) AS TBL

select * from @CaseSites

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-11-05T14:10:59Z

TimothyAWiseman answered Nov 5, '09

You can use a CTE or subquery to correlate and concatenate them the way you want and then do the update.

There are numerous methods of doing the concatenation. Some of them are discussed in Jeff Moden's great article: http://www.sqlservercentral.com/articles/Test+Data/61572/

And there is discussion of wrapping that in a CTE at: http://www.sqlservercentral.com/articles/Common+Table+Expression+(CTE)/62404/

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question