I am attempting to make a column in which it is a comma delimited list of other columns where the id is the same. How can I take the query I currently have, and update it to get the output that I expect?
T1
| PK |
|---|
| 1 |
| 2 |
T2
| ID | Name | T1.PK |
|---|---|---|
| 1 | Apple | 1 |
| 2 | Pear | 1 |
| 3 | Orange | 1 |
| 4 | Cat | 2 |
| 5 | Dog | 2 |
My hope is I can join these tables and get the following result.
| PK | Name |
|---|---|
| 1 | Apple, Pear, Orange |
| 2 | Cat, Dog |
I have a rough idea of how to accomplish this, but I do not see how to separate the list, instead I will get Apple, Pear, Orange, Cat, Dog for each row instead of them being separated. Here is the query I am currently working with.
select t1.PK,
stuff(
(select distinct ', ' Name
from t2
for XML PATH ("")), 1, 2, '') Name
from t1
left join t2
CodePudding user response：
In new SQL Server DB's (since SQL Server 2017), it's also possible to use STRING_AGG for this purpose:
SELECT t1.pk, STRING_AGG (t2.name,', ') AS name
FROM t1 JOIN t2 ON t1.pk = t2.[t1.pk]
GROUP BY t1.pk;
CodePudding user response：
select t1.PK,
stuff(
(select ', ' Name
from t2
where t2.[t1.PK] = t1.PK
for XML PATH ('')), 1, 1, '') Name
from t1
I answered this question about 12 years ago