Want to combine multiple rows of a distinct query to 1 line per distinct entry

I want to join the results of a distinct query so that I get 1 line per distinct entry. Let's say my table looks like this:

 IDCode  Value
 ID1     1
 ID1     2
 ID1     3
 ID2     1
 ID2     2

I want the result of my query to be:

 IDCode  Values
 ID1     1,2,3
 ID2     1,2

Can this be done in entirely in SQL?

more ▼

asked Aug 10, 2012 at 07:38 PM in Default

avatar image

0 1 1 3

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

1 answer: sort voted first

Yes, it can.

There's a handy trick with the XML handler that can be used to pull this stunt, and I've blogged about it in the past: http://thelonedba.wordpress.com/2011/02/22/string-concatenation-using-xml-path/


Damn. My blog post doesn't go far enough. Apologies... hang on...


OK, so the thing to do is to apply what I've done in the above post to each distinct ID. So something like this:

 declare @IDCodeVals TABLE (IDCode char(3), value int)
 insert into @IDCodeVals values ('ID1', 1), ('ID1', 2), ('ID1', 3), ('ID2', 1), ('ID2', 2)
 SELECT  IDCode,  stuff(valuelist, 1, 1, '') AS [Values]
 FROM (select distinct IDCode FROM @IDCodeVals) AS val1
 SELECT ',' + convert(varchar(max), value)
 FROM @IDCodeVals val2 WHERE val2.IDCode = val1.IDCode 
 FOR XML PATH ('')) D ( valuelist)

and that gives us:

 IDCode  Values
 ID1     1,2,3
 ID2     1,2
more ▼

answered Aug 10, 2012 at 07:47 PM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

...and there you have it, Alan. Hope that helps.

Aug 10, 2012 at 08:13 PM ThomasRushton ♦♦

Awesome. Thank you.

Aug 10, 2012 at 08:36 PM alan_t_lau
(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



Answers and Comments

SQL Server Central

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



asked: Aug 10, 2012 at 07:38 PM

Seen: 788 times

Last Updated: Aug 10, 2012 at 08:38 PM

Copyright 2018 Redgate Software. Privacy Policy