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

alan_t_lau gravatar image

alan_t_lau
0 1 1 2

(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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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

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:

x6

asked: Aug 10, 2012 at 07:38 PM

Seen: 625 times

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