sql-server - TSQL - 多列上的动态数据透视

Question

在下面的屏幕截图中，您可以看到我的来源和所需的输出表。

我无法对“产品”、“年份”或“度量”列进行硬编码，因为它们可以动态增长。“1”、“2”等列代表一个月，可以硬编码。

如果您能向我展示动态查询或向我指出类似的解决方案，我会很高兴。

这是源代码的小提琴。

谢谢你。

标签： sql-servertsqlsql-server-2012pivot

Answer 1

尝试以下操作：

--Data
DROP TABLE IF EXISTS TEMP
DROP TABLE IF EXISTS test

CREATE TABLE test
    ([Product] varchar(5), [Year] varchar(4),[Measure] varchar(10), 
     [1] int, [2] int, [3] int, [4] int,[5] int, [6] int,[7] int, [8] int,[9] int, [10] int,[11] int, [12] int)
;

INSERT INTO test
    ([Product], [Year], [Measure], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
VALUES
    ('ABCD', '2017','efgh', 1, 11,21,31,41,51,61,71,81,91,100,110),
    ('ABCD', '2017','efgh', 2, 12,21,31,41,51,61,71,81,91,100,110),
    ('ABCD', '2018','mnop', 3, 13,21,31,41,51,61,71,81,91,100,110),
    ('ABCD', '2018','mnop', 4, 14,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2017','efgh', 5, 15,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2017','efgh', 6, 16,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2018','mnop', 7, 17,21,31,41,51,61,71,81,91,100,110),
    ('WXYZ', '2018','mnop', 8, 18,21,31,41,51,61,71,81,91,100,110)
;

--UNPIVOTING MONTHS (As fixed months that is why using non-dynamic unpivot)
DROP TABLE IF EXISTS TEMP
SELECT Product, [Year], Measure, [Month], [Value]
INTO TEMP
FROM
(
  SELECT Product, [Year], Measure
  ,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
   FROM test
) AS t
UNPIVOT
(
  [Value] FOR [Month] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
 ) AS up;

select * from test

--Dynamic  Pivoting
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
                 (
                     SELECT DISTINCT 
                            ','+ Measure
                     FROM TEMP c FOR XML PATH(''), TYPE
                 ).value('.', 'nvarchar(max)'), 1, 1, '');
print @cols
SET @query = 'SELECT Product, Year, [Month], '+@cols+' from 
                (SELECT 
                   Product,
                   Year,
                   [Month],
                   [Value],
                   Measure AS Category
                FROM TEMP
                )x 
                pivot 
                (
                    sum([Value]) for Category in ('+@cols+')
                ) p
                order by 1,2, convert(int, [Month])';
print @query
EXECUTE (@query);

DROP TABLE IF EXISTS TEMP
DROP TABLE IF EXISTS test