SQL 行轉列 與 列轉行

先準備點數據：

CREATE TABLE Sell(  [Year] INT,  [Quarter] NVARCHAR(10),  Quantity INT)GOINSERT INTO SellSELECT 2006, 'Q1', 20 UNION ALLSELECT 2006, 'Q2', 15 UNION ALLSELECT 2006, 'Q2', 4 UNION ALLSELECT 2006, 'Q3', 12 UNION ALLSELECT 2006, 'Q4', 18 UNION ALLSELECT 2007, 'Q1', 10 UNION ALLSELECT 2007, 'Q2', 10 UNION ALLSELECT 2008, 'Q1', 8 UNION ALLSELECT 2008, 'Q2', 7 UNION ALLSELECT 2008, 'Q3', 5 UNION ALLSELECT 2008, 'Q3', 10 UNION ALLSELECT 2008, 'Q4', 9GO

1.      何為行列互換

簡單地說，就是實現如下形式的相互轉換

行轉列是將某行中的值作為了標題，而列轉行就是將標題變為了列中的值。

2.      行轉列

1）  靜態
靜態適合於要轉換為行的列值是確定的一些值，或值不是很多的情況

 

-- MSSQL 2005+ Pivot 靜態（行轉列）SELECT * FROM   sell PIVOT( SUM(quantity) FOR [quarter] IN ( Q1, Q2, Q3, Q4 ) ) p-- MSSQL 2000+ 靜態（行轉列）SELECT[Year],SUM(CASE [Quarter] WHEN N'Q1' THEN Quantity ELSE 0 END) Q1,SUM(CASE [Quarter] WHEN N'Q2' THEN Quantity ELSE 0 END) Q2,SUM(CASE [Quarter] WHEN N'Q3' THEN Quantity ELSE 0 END) Q3,SUM(CASE [Quarter] WHEN N'Q4' THEN Quantity ELSE 0 END) Q4FROM SellGROUP BY [Year]

2）  動態
當要轉換為行的列值不確定或非常多的時候，可以用動態 SQL

-- MSSQL 2005+ Pivot 動態（行轉列）DECLARE @sql VARCHAR(1000), @distinct_val VARCHAR(500) ;SET @sql = 'SELECT * FROM sell PIVOT(SUM(quantity) FOR [quarter] IN (' ;SET @distinct_val = (SELECT STUFF(( SELECT DISTINCT ',' + [quarter] FROM sell FOR XML PATH('')), 1, 1, '')) ;SET @sql = @sql + @distinct_val + '))' + ' p'EXEC(@sql)-- MSSQL 2000+ 動態（行轉列）DECLARE @sql VARCHAR(2000)SET @sql = 'SELECT [Year]'SELECT @sql = @sql + ',SUM(CASE [Quarter] WHEN ''' + [Quarter] + ''' THEN [Quantity] ELSE 0 END) ' + [Quarter]FROM sellGROUP BY [Quarter]SET @sql = @sql + ' FROM Sell GROUP BY [Year]'EXEC(@sql)

3.      列轉行

列轉行用動態比較麻煩，而且不好把控新的標題，其實實現的思路和上面行轉列一樣，就是找規律構造SQL。這裏僅給出靜態（假設上面語句的結果已保存在表 Sell_2 中）：

-- 用 unpovitselect [Year],[Quarter],[Quantity]from sell_2 unpivot(Quantity for [Quarter] in(Q1,Q2,Q3,Q4)) b-- 不用 unpivot（静态）select [Year],'Q1' [Quarter], Q1 [Quantity] from Sell_2 union allselect [Year],'Q2' [Quarter], Q2 [Quantity] from Sell_2 union allselect [Year],'Q3' [Quarter], Q3 [Quantity] from Sell_2 union allselect [Year],'Q4' [Quarter], Q4 [Quantity] from Sell_2order by 1,2

4.      小結

1）  Pivot 比select .. Case ..更簡潔，如果DB是MSSQL 2005或以上推薦用Pivot；

2） Pivot 大致的執行過程：先按未出現在Pivot表達時中的列進行分組，再按in中的枚舉項分別聚合。其實和select case when 類似。

3）  當列值不確認時，可以構造動態SQL實現；

4）  列轉行 Pivot和select .. Case…的性能差不多，但行轉列，用unPivot 相比 union [all]性能優勢非常明顯，畢竟後者要多次訪問表，而且unPivot還更簡潔。

5） Pivot 和 unPiovt 並非完全可逆，因為Pivot的時候，行已經被聚合過了。