Unpivot 將行轉換為列以及將列轉換為行

我試圖理解網路上的 unpivot 範例，但它們並沒有完全導致我想要做的事情，即將特定的行變成列**，**將列變成行。為了嘗試說明我的意思，我附上了一張圖片……

我相信給定列中的數據需要是相同的類型，所以如果是這種情況，那麼將所有列都設置為 varchar 是可以的。源表中的數據不需要是任何總和的一部分，只需按原樣顯示。我只是想“旋轉”它以垂直方式顯示數據以供最終使用者使用。

此查詢同時使用UNPIVOT和 PIVOT：

SELECT piv.[Data], piv.x, piv.y
FROM (
   SELECT [Type], ColA = CAST(ColA as varchar(10)), ColB = CAST(ColB as varchar(10)), ColC = CAST(ColC as varchar(10)), ColD = CAST(ColD as varchar(10))
   FROM @data
) d
UNPIVOT (
   [value] FOR [Data] IN (ColA, ColB, ColC, ColD)
) as unpiv
PIVOT (
   MAX([value]) 
   FOR [Type] IN ([x], [y])
) as piv
;

• 它首先對您的數據進行反透視以獲得正常表
• 然後它將這個表轉回新的所需格式

我必須將所有內容都轉換為 varchar。根據您的數據模型，您可能必須將它們轉換為另一種類型。PIVOT 需要聚合函式。由於這是一對一的匹配，它適用於 MAX 或 MIN。

使用SQL Server >= 2008，您還可以將UNPIVOT替換為Table Value Constructor和CROSS APPLY以及PIVOT：

SELECT piv.[Data], piv.x, piv.y
FROM (
   SELECT v.[Type], [value], [Data]
   FROM @data d
   CROSS APPLY (values
       (d.[type], CAST(d.ColA as varchar(10)), 'ColA')
       , (d.[type], CAST(d.ColB as varchar(10)) , 'ColB')
       , (d.[type], CAST(d.ColC as varchar(10)), 'ColC')
       , (d.[type], CAST(d.ColD as varchar(10)), 'ColD')
   ) as v([type], [value], [Data])
) unpiv
PIVOT (
   MAX([value])
   FOR [Type] IN ([x], [y])
) as piv
;

如果沒有PIVOT / UNPIVOT，您可以將此查詢與任何（舊）版本的 SQL Server 一起使用：

SELECT [Data]
   , [x] = MAX(CASE WHEN [type] = 'x' THEN [value] END)
   , [y] = MAX(CASE WHEN [type] = 'y' THEN [value] END)
FROM (
   SELECT [type], [value] = CAST(ColA as varchar(10)), [Data] = 'ColA' FROM @data
   UNION ALL
   SELECT [type], [value] = CAST(ColB as varchar(10)), [Data] = 'ColB' FROM @data
   UNION ALL
   SELECT [type], [value] = CAST(ColC as varchar(10)), [Data] = 'ColC' FROM @data
   UNION ALL
   SELECT [type], [value] = CAST(ColD as varchar(10)), [Data] = 'ColD' FROM @data
) as v
GROUP BY [Data]

輸出：

Data    x       y
ColA    123456  654321
ColB    $500    $200
ColC    6       36
ColD    30      90

數據：

Declare @data table([Type] char(1), ColA bigint, ColB varchar(10), ColC int, ColD int);
INSERT INTO @data([Type], ColA, ColB, ColC, ColD) VALUES
   ('x', 123456, '$500', 6, 30)
   , ('Y', 654321, '$200', 36, 90);

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