2

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?

Taryn
  • 242,637
  • 56
  • 362
  • 405
kmfdm
  • 79
  • 1
  • 5
  • 12

1 Answers1

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 ]

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 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? – kmfdm Jul 30 '13 at 23:45
  • @kmfdm: the size if the groups doesn't matter. – Tim Schmelter Jul 30 '13 at 23:50
  • 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 http://stackoverflow.com/a/12940952/1744834 for example. – Roman Pekar Jul 31 '13 at 12:19