x

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

alan_t_lau
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/

--edit--

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

--edit--

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
 CROSS APPLY (
 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 ♦♦
40.3k 20 49 53

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x9

asked: Aug 10, 2012 at 07:38 PM

Seen: 781 times

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

Copyright 2016 Redgate Software. Privacy Policy