SQL Server中具有多列的动态数据透视表

一尘不染

SQL Server中具有多列的动态数据透视表

sql

我正在尝试动态地旋转表，但无法获得所需的结果。这是创建表的代码

 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)'

到目前为止编写的代码....这将列套和jib_in旋转为行，但这仅是两个ROWS，即我在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;

我需要所有列进行旋转并显示在旋转表上。任何帮助，将不胜感激。我是动态枢纽领域的新手。我尝试了很多添加其他列的方法，但无济于事！！我知道还有其他方法，如果有其他方法可以解决这个问题，请随时提及。

阅读 145

2021-05-05

共1个答案

一尘不染

请使用它（如果遇到排序规则问题，请更改所有3种INT数据类型）：

静态代码：

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

使用动态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)

2021-05-05