使用 Pivot/Unpivot 將列轉換為行的 SQL 查詢

我的選擇表。我嘗試使用 Pivot 查詢來獲得預期的結果，但我是 struct 來生成預期的結果。

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
             SELECT 'A',2000, 'X',10,2
   UNION ALL SELECT 'A', 2001, 'X',21,1
   UNION ALL SELECT 'A', 2000, 'Y',13,3
   UNION ALL SELECT 'A', 2001, 'Y',2,3
   UNION ALL SELECT 'B', 2000, 'X',0,2
   UNION ALL SELECT 'B', 2001, 'X',5,1
   UNION ALL SELECT 'B', 2000, 'Y',6,4
   UNION ALL SELECT 'B', 2001, 'Y',9,5
   UNION ALL SELECT 'C', 2000, 'X',3,5
   UNION ALL SELECT 'C', 2001, 'X',2,8
   UNION ALL SELECT 'C', 2000, 'Y',9,2
   UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select *  from PersonOptions

ID  Year    Text    Value1  Value2
A   2000    X        10       2
A   2001    X        21       1
A   2000    Y        13       3
A   2001    Y        2        3
B   2000    X        0        2
B   2001    X        5        1
B   2000    Y        6        4
B   2001    Y        9        5
C   2000    X        3        5
C   2001    X        2        8
C   2000    Y        9        2
C   2001    Y        8        3

預期輸出：

              Value1          Value2   
ID  Text    2000    2001    2000    2001
A    X       10      2       21      1
A    Y       13      3       2       3
B    X        0      2       5       1
B    Y        6      4       9       5
C    X        3      5       2       8
C    Y        9      2       8       3

解決這個問題的一種方法是做兩個樞軸並將它們連接在一起。見下文。根據輸入數據，我還相信您的預期輸出範例略有錯誤，但這並不重要。

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
         SELECT 'A',2000, 'X',10,2
UNION ALL SELECT 'A', 2001, 'X',21,1
UNION ALL SELECT 'A', 2000, 'Y',13,3
UNION ALL SELECT 'A', 2001, 'Y',2,3
UNION ALL SELECT 'B', 2000, 'X',0,2
UNION ALL SELECT 'B', 2001, 'X',5,1
UNION ALL SELECT 'B', 2000, 'Y',6,4
UNION ALL SELECT 'B', 2001, 'Y',9,5
UNION ALL SELECT 'C', 2000, 'X',3,5
UNION ALL SELECT 'C', 2001, 'X',2,8
UNION ALL SELECT 'C', 2000, 'Y',9,2
UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select * INTO #t from PersonOptions

;WITH pvt1 AS
(
   SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
   FROM 
   (
       SELECT t.ID, t.Year, t.Text, t.Value1
       FROM #t t
   ) x
   PIVOT
   (
       MAX(Value1) FOR Year IN ([2000], [2001])
   ) pvt
), pvt2 AS
(
   SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
   FROM 
   (
       SELECT t.ID, t.Year, t.Text, t.Value2
       FROM #t t
   ) x
   PIVOT
   (
      MAX(Value2) FOR Year IN ([2000], [2001])
   ) pvt
)
SELECT * 
FROM pvt1
INNER JOIN pvt2 ON pvt2.ID = pvt1.ID AND pvt2.Text = pvt1.Text


DROP TABLE #t

引用自：https://dba.stackexchange.com/questions/217587