question

alan_t_lau avatar image
alan_t_lau asked

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?
ms sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
...and there you have it, Alan. Hope that helps.
0 Likes 0 ·
alan_t_lau avatar image alan_t_lau commented ·
Awesome. Thank you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.