Concatenate Multiple rows of a table in SQL Server 2014 / SQL Server 2016

Question

I have a table like this :

id	movie	actorid	actor	roleid	rolename
1	mi3	121	tom	6	actor
2	avenger	104	scarlett	4	actress
2	avenger	3	russo	2	action director

I'm expecting the output like :

id	movie	actorid	actor	roleid	rolename
1	mi3	121	tom	6	actor
2	avenger	104,3	scarlett,russo	4,2	actress, action director

For latest SQL Server version, I saw the STRING_AGG function to concatenate columns or row data. But how can I achieve the expected output with SQL Server 2014 using STUFF ?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f09d4166-2030-41fe-b86e-392fbc94db53/tsql-equivalent-for-groupconcat-function?forum=transactsql — P.Salmon, Jan 07 '20 at 07:35
Personally I would suggest not doing this at all. You're data will end up being very messy, and SQL Server has no concept (natively) of ordinal positions in delimited strings. — Thom A, Jan 07 '20 at 07:49
Hey Larnu, thanks for the suggestion. Actually i want to display this each record in a tag.. in a foreach loop. and this data is limited say some 142 rows .. — Viraj Kawthankar, Jan 07 '20 at 07:58

score 1 · Accepted Answer · answered Jan 07 '20 at 07:35

Try this:

DECLARE @DataSource TABLE
(
    [id] INT
   ,[movie] VARCHAR(12)
   ,[actiorid] INT
   ,[actor] VARCHAR(12)
   ,[roleid] INT
   ,[rolename] VARCHAR(36)
);

INSERT INTO @DataSource ([id], [movie], [actiorid], [actor], [roleid], [rolename])
VALUES (1, 'mi3 ', 121, 'tom ', 6, 'actor')
      ,(2, 'avenger', 104, 'scarlett', 4, 'actress')
      ,(2, 'avenger', 3, 'russo', 2, 'action director');

-- SQL Server 2017
SELECT [id] 
      ,[movie]
      ,STRING_AGG([actiorid], ',') AS [actorid]
      ,STRING_AGG([actor], ',') AS [actor]
      ,STRING_AGG([roleid], ',') AS [roleid]
      ,STRING_AGG([rolename], ',') AS [rolename]
FROM @DataSource
GROUP BY [id]
        ,[movie];

-- SQL Server
WITH DataSoruce AS
(
    SELECT DISTINCT [id] 
                   ,[movie]
    FROM @DataSource
)
SELECT *
FROM DataSoruce A
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT DISTINCT ',' + CAST([actiorid] AS VARCHAR(12))
            FROM @DataSource S
            WHERE A.[id] = S.[id]
                AND A.[movie] = S.[movie]
            FOR XML PATH, TYPE 
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) R1 ([actiorid])
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT DISTINCT ',' + CAST([actor] AS VARCHAR(12))
            FROM @DataSource S
            WHERE A.[id] = S.[id]
                AND A.[movie] = S.[movie]
            FOR XML PATH, TYPE 
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) R2 ([actor])
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT DISTINCT ',' + CAST([roleid] AS VARCHAR(12))
            FROM @DataSource S
            WHERE A.[id] = S.[id]
                AND A.[movie] = S.[movie]
            FOR XML PATH, TYPE 
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) R3 ([roleid])
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT DISTINCT ',' + CAST([rolename] AS VARCHAR(12))
            FROM @DataSource S
            WHERE A.[id] = S.[id]
                AND A.[movie] = S.[movie]
            FOR XML PATH, TYPE 
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) R4 ([rolename]);

Concatenate Multiple rows of a table in SQL Server 2014 / SQL Server 2016

1 Answers1