时间戳

首页 > 解决方案 > 如何在 SQL Server 中将列中的未知值转换为行

sql - 如何在 SQL Server 中将列中的未知值转换为行

问题描述

如何将列中的未知值转换为具有固定列的行并在 SQL Server 的另一列中求和值？

这个例子

   | Id | Date | Amount1 | Amount2
   +----+------+---------+--------
   | 1  | 2018 | 1000    | 100
   | 2  | 2018 | 2000    | 200
   | 2  | 2018 | 3000    | 300
   | 1  | 2019 | 4000    | 400
   | 1  | 2019 | 5000    | 500
   | .. | .... | ....    | ...

我要这个

| Id | 2018Amount1 | 2018Amount2 | 2019Amount1 | 2019Amount2 | ...
+----+-------------+-------------+-------------+-------------+-------
| 1  |    1000     |     100     |   9000      |   900       | ...
| 2  |    5000     |     500     |   0         |   0         | ...

标签： sqlsql-serversql-server-2012

解决方案

您可以使用动态枢轴

DECLARE @ColName NVARCHAR(MAX) =''
SELECT @ColName = @ColName + ', '+ ColName
FROM (  SELECT  DISTINCT  QUOTENAME( CAST([Date] AS VARCHAR(4)) + 'Amount1') 
            + ', ' + QUOTENAME(CAST([Date] AS VARCHAR(4)) + 'Amount2') ColName FROM TestTable )T

SET @ColName = STUFF (@ColName,1,1,'')

DECLARE @SqlText NVARCHAR(MAX) = 
'SELECT * FROM (
    SELECT Id, CAST([Date] AS VARCHAR(4)) + ''Amount1'' AS [Date], Amount1 AS Amount FROM TestTable
    UNION 
    SELECT Id, CAST([Date] AS VARCHAR(4)) + ''Amount2'' [Date], Amount2 AS Amount FROM TestTable
) SRC
PIVOT(SUM(Amount) FOR Date IN (' + @ColName+ ') ) AS PVT'

EXECUTE sp_executesql @SqlText

推荐阅读