sql server中具有多列的动态数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21325394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic pivot table with multiple columns in sql server
提问by sql84
I am trying to pivot table DYNAMICALLY but couldn't get the desired result. Here is the code to create a table
我正在尝试 DYNAMICALLY 数据透视表，但无法获得所需的结果。这是创建表的代码
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
creation int
)
insert into Report values
('A_1',0.345,0,0,1.23,20140212),
('B_2',0.456,0,4,2.34,20140215),
('C_3',0.554,0,6,0.45,20140217),
('D_4',0.231,0,8,7.98,20140222),
('E_5',0.453,0,0,5.67,20140219),
('F_6',0.344,0,3,7.23,20140223)'
Code written so far.... this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
到目前为止编写的代码......这将列甲板和 jib_in 转为行，但这只是两行，即我放在 PIVOT 函数下的聚合函数中的一个和我放在 QUOTENAME() 中的一个
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT p.deck, p.jib_in
FROM dbo.report AS p
) AS j
PIVOT
(
SUM(jib_in) FOR deck IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
I need all the columns to be pivoted and show on the pivoted table. any help would be appreciated. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!! I know there are other ways please feel free to mention if there is any other way to get this right.
我需要旋转所有列并显示在旋转表上。任何帮助，将不胜感激。我对动态枢轴很陌生。我尝试了很多方法来添加其他列但无济于事！我知道还有其他方法，请随时提及是否有其他方法可以解决这个问题。
采纳答案by A.J
Please use this (If you are getting Collation issue, please change all the 3 INT datatypes):
请使用这个（如果您遇到整理问题，请更改所有 3 种 INT 数据类型）：
STATIC code:
静态代码：
SELECT HEADER, [A_1],[B_2],[C_3],[D_4],[E_5],[F_6]
FROM
(SELECT DECK,HEADER, VALUE FROM REPORT
UNPIVOT
(
VALUE FOR HEADER IN ([JIB_IN],[REV],[REV_INSIGHT],[JIB_OUT],[CREATION])
) UNPIV
) SRC
PIVOT
(
SUM(VALUE)
FOR DECK IN ([A_1],[B_2],[C_3],[D_4],[E_5],[F_6])
) PIV
Using Dynamic SQL:
使用动态 SQL：
DECLARE @COLSUNPIVOT AS NVARCHAR(MAX),
@QUERY AS NVARCHAR(MAX),
@COLSPIVOT AS NVARCHAR(MAX)
SELECT @COLSUNPIVOT = STUFF((SELECT ','+QUOTENAME(C.NAME)
FROM SYS.COLUMNS AS C
WHERE C.OBJECT_ID = OBJECT_ID('REPORT') AND C.NAME <> 'DECK'
FOR XML PATH('')), 1, 1, '')
SELECT @COLSPIVOT = STUFF((SELECT ',' + QUOTENAME(DECK)
FROM REPORT T FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
SET @QUERY
= 'SELECT HEADER, '+@COLSPIVOT+'
FROM
(
SELECT DECK,HEADER,VALUE FROM REPORT
UNPIVOT
(
VALUE FOR HEADER IN ('+@COLSUNPIVOT+')
) UNPIV
) SRC
PIVOT
(
SUM(VALUE)
FOR DECK IN ('+@COLSPIVOT+')
) PIV'
EXEC(@QUERY)
