Pivot SQL by Year

Question

嗯，有很多关于pivot SQL的引用。但是，我还是解决不了我的问题。

-----------------------------------------------------------------------
InspectYear     PartNo    PartDesc    A       B      C     D
-----------------------------------------------------------------------
2015            001       Part 1      9       8      8     6
2015            002       Part 2      8       8      8     6
2014            001       Part 1      9       2      8     6
2014            002       Part 2      8       8      8     4

我想用动态" InspectYear "得到以下结果:

-----------------------------------------------------------------------
PartNo    PartDesc  NewCol        2014    2015
-----------------------------------------------------------------------
001       Part 1    A             9       9
001       Part 1    B             2       8
001       Part 1    C             8       8
001       Part 1    D             6       6
002       Part 2    A             8       8
002       Part 2    B             8       8
002       Part 2    C             8       8
002       Part 2    D             4       6

有人对透视查询有什么想法吗?
真的感激。

Answer 1

尝试动态PIVOT:

DECLARE @InspectYear AS NVARCHAR(MAX),@Query  AS NVARCHAR(MAX);
SET @InspectYear = STUFF((SELECT distinct ',' + QUOTENAME(InspectYear) 
            FROM table4 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
SET @Query = 
';WITH data AS
(
    SELECT InspectYear, PartNo, PartDesc, NewCol, number
    FROM table4
    UNPIVOT
    (
      number
      FOR NewCol IN (A, B, C,D)
    ) AS unpvt
)
SELECT *
FROM data
PIVOT
(
    SUM(number)
    FOR InspectYear IN ( '+@InspectYear+' )
) AS pvt
ORDER BY PartNo, PartDesc, NewCol '
EXECUTE(@query)

注:请参考@doraemon创建表并插入数据语句

Answer 2

您必须将初始数据UNPIVOT，然后再PIVOT。

这是我找到的解决方案。看看这是否符合你的要求。

CREATE TABLE table4
(
    InspectYear INT,
    PartNo VARCHAR(5),
    PartDesc VARCHAR(30),
    A INT,
    B INT,
    C INT,
    D INT
)
INSERT INTO table4 VALUES
(2015, '001', 'Part 1', 9, 8, 8, 6),
(2015, '002', 'Part 2', 8, 8, 8, 6),
(2014, '001', 'Part 1', 9, 2, 8, 6),
(2014, '002', 'Part 2', 8, 8, 8, 4)
;WITH data AS
(
    SELECT InspectYear, PartNo, PartDesc, NewCol, number
    FROM table4
    UNPIVOT
    (
      number
      FOR NewCol IN (A, B, C,D)
    ) AS unpvt
)
SELECT *
FROM data
PIVOT
(
    SUM(number)
    FOR InspectYear IN ( [2014], [2015] )
) AS pvt
ORDER BY PartNo, PartDesc, NewCol

请试一下。谢谢你！:)