sql - sql中如何对表进行转置

Question

我正在寻找一种简单的方法来执行此转置:

date    ID  Vr    val 
1.1.14   1   2000  50
1.1.14   2   2000  60
1.1.14   2   2001  100

date ID  vr2000 vr2001
1.1.14 1   50      0
1.1.14 2   60     100

我有大约 83 个变量和超过 100 万条记录的数据。我想将“vr”连接到行中的索引变量。有什么建议吗？

Answer 1

这可以通过使用动态 sql 查询并使用 sp_executesql 存储过程执行动态构建的查询来完成。

-- Create table Variable
CREATE TABLE [Variable](
    [date] [nvarchar](50) NULL,
    [ID] [int] NULL,
    [vr] [int] NULL,
    [val] [int] NULL
)

--Insert sample records
INSERT [dbo].[Variable] ([date], [ID], [vr], [val]) 
VALUES  (N'1.1.14', 1, 2000, 50),
        (N'1.1.14', 2, 2000, 60),
        (N'1.1.14', 2, 2001, 100)

GO
--QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnNamesInPivot AS NVARCHAR(MAX);
DECLARE @ColumnNamesInSelect AS NVARCHAR(MAX);

--Get distinct values of PIVOT Column 
SELECT TOP 100 PERCENT
        @ColumnNamesInPivot = ISNULL(@ColumnNamesInPivot + ',', '')
        + QUOTENAME(vr),
        @ColumnNamesInSelect = ISNULL(@ColumnNamesInSelect + ',', '')
        + 'ISNULL(' + QUOTENAME(vr) + ',0) AS [vr' + CAST(vr AS NVARCHAR(50))
        + ']'
FROM    ( SELECT DISTINCT
                    vr
          FROM      Variable
        ) AS P
ORDER BY vr ASC;

--Prepare the PIVOT query using the dynamic query
SET @DynamicPivotQuery = N'Select date,ID,' + @ColumnNamesInSelect + ' 
            FROM    ( SELECT * 
          FROM      Variable
        ) AS SourceTable PIVOT( MAX(val) FOR vr IN (' + @ColumnNamesInPivot
    + ') ) AS PVTTable';

--PRINT @DynamicPivotQuery;
EXEC sp_executesql @DynamicPivotQuery;

结果如下: