Output a comma-separated list in a column in SQL Server [duplicate]

Question

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

Taryn · Accepted Answer · 2012-10-24 16:03:31Z

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

answered Oct 24, 2012 at 16:03

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

2 Comments

beauk Over a year ago

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.

Taryn Over a year ago

@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

Collectives™ on Stack Overflow

Output a comma-separated list in a column in SQL Server [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related