how to self join a table to get more info

Question

Possible Duplicate:
Concatenate many rows into a single text string?
How to use GROUP BY to concatenate strings in SQL Server?

I have a table customer (id, person_id, account number), id is primary key. For example

id   person_id    account number
1    0001         acc00011
2    0001         acc00012
3    0002         acc00021

Now I want to write a query to self join the table to get all the account numbers for each person. The result table should be

person_id    account
0001         acc00011, acc00012
0002         acc00021

How do I write the query?

score 3 · Accepted Answer · edited May 23 '17 at 11:43

3

You can't do that unless you want accounts concatenated in a single string by person_id. See this questions:

edited May 23 '17 at 11:43

Community

answered Sep 24 '12 at 16:31

Anri

I tried the answer for the first link, it works. thanks. – GLP Sep 24 '12 at 16:45

score 3 · Answer 2 · answered Sep 24 '12 at 16:47

3

Use a correlated subquery with FOR XML PATH and GROUP BY for the concatenation. Use STUFF to remove preceding comma from results.

answered Sep 24 '12 at 16:47

Bryan

2 Answers2