1

Possible Duplicate:
Simulating group_concat MySQL function in SQL Server 2005?

What's the best way to achieve this? Edit: in MSSQL 2005

Table

a   |  b
------------
x   |  1
x   |  4
y   |  6
y   |  1

Query:

SELECT ? FROM Table

so that output is :

a   | ListOfB
------------
x   | 1, 4
y   | 6, 1
beauk
  • 178
  • 3
  • 16

1 Answers1

9

In SQL Server you can use FOR XML PATH:

select distinct a,
  stuff(
  (
    select ','+ cast(b as varchar(10)) 
    from table1 t2 
    where t1.a = t2.a for XML path('')
  ),1,1,'') ListOfB
from table1 t1

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • To add a bit of complexity, what if t1 and t2 both need to have significant WHERE clauses? I tried it with the same where clauses in both, which worked, but I wonder if there is a better way. Maybe others use temp tables? Basically Table1 is HUGE, and I only need about .01 percent back. so I don't like the idea of doing the sub query on the huge table. I think I will use a temp table to the the date filtered into essentially Table1, so I can use nearly exactly the query in the Answer. – beauk Oct 24 '12 at 19:13
  • @AncientAnt you would need to use the `where` at least on the outer one, you would have to test if you needed it on the inner query – Taryn Oct 24 '12 at 19:19