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

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.