SQL:将一列数据转换为多列

Question

我曾尝试使用诸如 Pivot 之类的东西来寻找解决我的问题的方法，但它们似乎没有提供我正在寻找的输出，因为它们似乎将一行中的特定值映射到一列。

我有两列，第一列包含“关键”字段，第二列正在更改数据。例如

╔══════════╦══════════════════╗
║ Project  ║     Location     ║
╠══════════╬══════════════════╣
║ ProjectA ║ \\Server1\Share1 ║
║ ProjectA ║ \\Server2\Share1 ║
║ ProjectB ║ \\Server6\Share2 ║
║ ProjectB ║ \\Server1\Share2 ║
║ ProjectB ║ \\Server2\Share3 ║
║ ProjectC ║ \\Server8\Share2 ║
║ ProjectD ║ \\Server5\Share9 ║
║ ProjectD ║ \\ServerX\ShareY ║
╚══════════╩══════════════════╝

我试图实现的输出如下:

╔══════════╦══════════════════╦══════════════════╦══════════════════╦═════════╦══════════╦═════════╗
║ Project  ║     Column1      ║     Column2      ║     Column3      ║ Column4 ║ Column5  ║ ColumnX ║
╠══════════╬══════════════════╬══════════════════╬══════════════════╬═════════╬══════════╬═════════╣
║ ProjectA ║ \\Server1\Share1 ║ \\Server2\Share1 ║ NULL             ║ NULL    ║ NULL     ║         ║
║ ProjectB ║ \\Server1\Share2 ║ \\Server2\Share3 ║ \\Server6\Share2 ║ NULL    ║ NULL     ║         ║
║ ProjectC ║ \\Server8\Share2 ║ NULL             ║ NULL             ║ NULL    ║ NULL     ║         ║
║ ProjectD ║ \\Server5\Share9 ║ \\ServerX\ShareY ║ NULL             ║ NULL    ║ NULL     ║         ║
╚══════════╩══════════════════╩══════════════════╩══════════════════╩═════════╩══════════╩═════════╝

如果该列没有数据，那么它将为 NULL。

位置列中不同值的数量是动态的，所需的输出是通用列名称，在相应的项目值旁边有不同的位置值。

希望有人能帮我解决这个问题，因为它让我抓狂!

提前致谢。

忍者

Answer 1

警告:

此解决方案假定最多 6 列，您可以添加更多列，例如最多 20 列。

LiveDemo

数据:

CREATE TABLE #mytable(
   Project  VARCHAR(80) NOT NULL 
  ,Location VARCHAR(160) NOT NULL
);
INSERT INTO #mytable(Project,Location) VALUES ('ProjectA','\\Server1\Share1');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectA','\\Server2\Share1');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server6\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server1\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server2\Share3');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectC','\\Server8\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\Server5\Share9');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\ServerX\ShareY');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\ServerX\ShareY');

查询:

WITH cte AS
(
  SELECT Project, Location,
    [rn] = RANK() OVER (PARTITION BY Project ORDER BY Location)
  FROM #mytable
)
SELECT
   Project
  ,Column1 = MAX(CASE WHEN rn = 1 THEN Location ELSE NULL END)
  ,Column2 = MAX(CASE WHEN rn = 2 THEN Location ELSE NULL END)
  ,Column3 = MAX(CASE WHEN rn = 3 THEN Location ELSE NULL END)
  ,Column4 = MAX(CASE WHEN rn = 4 THEN Location ELSE NULL END)
  ,Column5 = MAX(CASE WHEN rn = 5 THEN Location ELSE NULL END)
  ,Column6 = MAX(CASE WHEN rn = 6 THEN Location ELSE NULL END)
  -- ....
  --  ,ColumnX = MAX(CASE WHEN rn = X THEN Location ELSE NULL END)
FROM cte
GROUP BY Project;

编辑:

使用 Dynamic-SQL 并生成 Pivoted 列列表的真正通用解决方案:

LiveDemo2

DECLARE @cols  NVARCHAR(MAX),
        @cols_piv NVARCHAR(MAX),
        @query NVARCHAR(MAX)
        ,@max  INT = 0;

SELECT @max = MAX(c)
FROM (
  SELECT Project, COUNT(DISTINCT Location) AS c
  FROM #mytable
  GROUP BY Project) AS s;


SET @cols = STUFF(     
            (SELECT ',' +  CONCAT('[',c.n, '] AS Column',c.n, ' ')
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @cols_piv = STUFF(
            (SELECT ',' +  CONCAT('[',c.n, '] ')
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');        

-- SELECT @cols;

set @query = N'SELECT Project, ' + @cols + ' from 
            (
                select Project, Location,
                [rn] = RANK() OVER (PARTITION BY Project ORDER BY Location)
                from #mytable
            ) x
            pivot 
            (
                max(Location)
                for rn in (' + @cols_piv + ')
            ) p ';

-- SELECT @query; 

EXEC [dbo].[sp_executesql]
    @query;