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

avatar image

graham.reeds
66 4 4 8

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 voted first

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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

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

avatar image

TimothyAWiseman
15.6k 22 49 38

(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

avatar image

Christopher Klein
101 1 2 3

(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.

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:

x1066
x501
x26

asked: Nov 05, 2009 at 01:03 PM

Seen: 21249 times

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

Copyright 2016 Redgate Software. Privacy Policy