Merge rows values if the FK is same

Question

I have a following table

PK	NAME	FK
01	abc	01FK
02	bca	01FK
03	cab	02FK
04	xyz	03FK
05	yzx	02FK

I want to generate a new table where FK is the primary and the rest of the column(s) merge based on FK. So the end result looks something like this:

FK	NAME
01FK	abc, bca
02FK	cab, yzx
03FK	xyz

• Erik Reasonable Rates Darling: Help me write this query in SQL
• user1822: Which DBMS product are you using?

Answer 1

You can use STRING_AGG to solve this problem (SQL Server 2017):

SELECT FK, STRING_AGG(name, ', ') AS NAME FROM dbo.NotProvidedTableName
GROUP BY FK

If you are on an older version, you can use the FOR XML clause.

WITH CTE_TableName
AS (SELECT FK,
 Name
 FROM dbo.NotProvidedTableName)
SELECT t0.FK,
 STUFF(
 (
 SELECT ',' + t1.Name
 FROM CTE_TableName t1
 WHERE t1.FK = t0.FK
 ORDER BY t1.Name
 FOR XML PATH('')
 ),
 1,
 LEN(','),
 ''
 ) AS NAME
FROM CTE_TableName t0
GROUP BY t0.FK
ORDER BY FK;

For next time, please add scripts to create tables and testdata please. See also the link Erik suggested as comment.