学步园

先準備點數據：

CREATE TABLE Sell
(
  [Year] INT,
  [Quarter] NVARCHAR(10),
  Quantity INT
)
GO

INSERT INTO Sell
SELECT 2006, 'Q1', 20 UNION ALL
SELECT 2006, 'Q2', 15 UNION ALL
SELECT 2006, 'Q2', 4 UNION ALL
SELECT 2006, 'Q3', 12 UNION ALL
SELECT 2006, 'Q4', 18 UNION ALL
SELECT 2007, 'Q1', 10 UNION ALL
SELECT 2007, 'Q2', 10 UNION ALL
SELECT 2008, 'Q1', 8 UNION ALL
SELECT 2008, 'Q2', 7 UNION ALL
SELECT 2008, 'Q3', 5 UNION ALL
SELECT 2008, 'Q3', 10 UNION ALL
SELECT 2008, 'Q4', 9
GO

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) Q4
FROM Sell
GROUP 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 sell
GROUP BY [Quarter]

SET @sql = @sql + ' FROM Sell GROUP BY [Year]'

EXEC(@sql)

3.      列轉行

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

-- 用 unpovit
select [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 all
select [Year],'Q2' [Quarter], Q2 [Quantity] from Sell_2 union all
select [Year],'Q3' [Quarter], Q3 [Quantity] from Sell_2 union all
select [Year],'Q4' [Quarter], Q4 [Quantity] from Sell_2
order 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的時候，行已經被聚合過了。