SQL枢轴1:M行为字符串列

Question

我有一个1:M记录集，我需要将它转到连接结果的列中。

创建示例数据：

CREATE TABLE #temptable 
(
    ID int,
    Division int,
    Material int
);

 insert into #temptable
 Values
 (999, 1, 1)
 ,(999, 1, 2)
 ,(999, 1, 3)
 ,(999, 2, 1)
 ,(999, 2, 6)
 ,(999, 3, 2)

样本数据：

ID     Division     Material   
----  ----------   ----------   
999       1            1          
999       1            2
999       1            3
999       2            1
999       2            6
999       3            2

我需要这样的结果，每个部门的材料都是连在一起的：

 ID   Division-1   Division-2   Division-3
----  ----------   ----------   ----------
999     1,2,3          1,6          2

Answer 1

试试这个：

SELECT ID,
       [1] AS 'Division-1',
       [2] AS 'Division-2',
       [3] AS 'Division-3'
FROM
(
    SELECT DISTINCT
           a.ID,
           a.Division,
           LEFT(r.ResourceName, LEN(r.ResourceName) - 1) ResourceName
    FROM #temptable a
         CROSS APPLY
    (
        SELECT CAST(Material AS VARCHAR(10))+','
        FROM #temptable t
        WHERE a.[ID] = t.[ID]
              AND a.Division = t.Division
        FOR XML PATH('')
    ) r(ResourceName)
) src PIVOT(MAX(ResourceName) FOR Division IN([1],
                                              [2],
                                              [3])) piv; 

简要说明我们需要在逗号分隔的结构上创建枢轴。

下面的查询将列值转换为逗号分隔的行。

SELECT a.ID,
       a.Division,
       LEFT(r.ResourceName, LEN(r.ResourceName) - 1) ResourceName
FROM #temptable a
     CROSS APPLY
(
    SELECT CAST(Material AS VARCHAR(10))+','
    FROM #temptable t
    WHERE a.[ID] = t.[ID]
          AND a.Division = t.Division
    FOR XML PATH('')
) r(ResourceName);

在那之后，我们创造了枢轴。