SQL query multiple rows into single row

Question

Is there any way to change a SQL query that would normally return multiple rows with the same values into a single row?

for example, if my existing query returns this:

ColA ColB
1    AA
1    BB
1    CC
2    AA
3    AA

could I change the query to have only 3 rows returned and to place the 2nd and 3rd result for 1 into the first row, so that you'd have a row of: 1 AA BB CC?

@kmfmd: I reverted to my edit with the inline table. I think it explains everything perfectly and then you don't need to worry about image hosts being blocked. — Cᴏʀʏ
– Cᴏʀʏ, Commented Jul 30, 2013 at 22:39
possible duplicate of Combine multiple results in a subquery into a single comma-separated value — Janty
– Janty, Commented Jul 31, 2013 at 12:17

Tim Schmelter · Accepted Answer · 2013-07-30 22:51:33Z

1

Not really nice but working(I'm missing group_concat from MySql):

SELECT ColA, 
       ColB=STUFF(
             (SELECT ' ' + ColB 
              FROM dbo.Table1 t2
              WHERE t1.ColA = t2.ColA 
              FOR XML PATH (''))
             , 1, 1, '')
FROM dbo.Table1 t1
GROUP BY ColA

Demo

[ Edit: for any reason presumed SQL-Server ]

edited Jul 30, 2013 at 22:51

answered Jul 30, 2013 at 22:46

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kmfdm Over a year ago

Yes, I am using MS SQL Server. I will give this a try tomorrow, thanks. Does that limit to 3 occurrences, though? Is there a way to generate it in the desired format regardless of how many occurrences you might have?

Tim Schmelter Over a year ago

@kmfdm: the size if the groups doesn't matter.

roman Over a year ago

I suggest to use value function, like this -

stuff((select ' ' + ColB from dbo.Table1 as t2 where t2.ColA = t1.ColA for xml path('')).value('.', 'nvarchar(max)'), 1, 1, '')

, otherwise your solution is not correct for arbitrary strings, like 'A&A', for example. Also see this answer stackoverflow.com/a/12940952/1744834 for example.

Collectives™ on Stack Overflow

SQL query multiple rows into single row

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related