x

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 ... 
more ▼

asked Nov 05, 2009 at 01:03 PM in Default

graham.reeds gravatar image

graham.reeds
66 4 4 5

TimothyAWiseman : why remove the SQL2000 tag?
Nov 05, 2009 at 02:44 PM Kev Riley ♦♦
I've added the tag back. No reason to remove it.
Nov 05, 2009 at 08:13 PM Kev Riley ♦♦
Because I goofed. I meant to add the other tags, not remove the original.
Nov 06, 2009 at 02:08 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest
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

more ▼

answered Nov 05, 2009 at 02:09 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

Heh... I'm confused, Kev... you post a 2k only question and then post a 2k5 answer. Which is it? A 2k or 2k5 problem?
Mar 07, 2010 at 01:09 AM Jeff Moden
Jeff, this isn't my question! The tags got messed around with a bit, and I agree this is a 2k5 only answer
Mar 12, 2010 at 06:52 PM Kev Riley ♦♦
Cool. This has just saved me a headache.
Oct 01, 2010 at 07:23 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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/

more ▼

answered Nov 05, 2009 at 02:10 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 20 23 32

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 05, 2009 at 01:36 PM

Christopher Klein gravatar image

Christopher Klein
101 2

Kev's version is cleaner :) I think that was my FIRST edit but I couldnt get STUFF to lose the last comma but I can see where ', ' (space inserted) works now. Yeah, OP had this tagged specifically as SQL2000... can't change the conditions to fit your answer Tim.
Nov 05, 2009 at 03:42 PM Christopher Klein
I didn't think Tim had retagged just so his answer fits!! :)
Nov 05, 2009 at 04:15 PM Kev Riley ♦♦
Heh. Completely slipped my mind that this was for 2000 by the time I added the CTE stuff.
Nov 06, 2009 at 02:10 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x476
x20

asked: Nov 05, 2009 at 01:03 PM

Seen: 17341 times

Last Updated: Sep 23, 2010 at 03:48 AM