sql-server - 在 SQL Server 2014 / SQL Server 2016 中连接表的多行

Question

我有一张这样的桌子：

 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

我期待这样的输出：

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

对于最新的 SQL Server 版本，我看到了STRING_AGG连接列或行数据的功能。但是如何使用 SQL Server 2014 实现预期的输出STUFF？

标签： sql-servertsqlstring-aggregation

Answer 1

试试这个：

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]);