老生常談SQL2005語句實現行轉列,列轉行

在做報表時，經常需要將資料表中的行轉列，或者列轉行，如果不知道方法，你會覺得通過SQL語句來實現非常難。這裡，我將使用pivot和unpivot來實現看似複雜的功能。這個功能在sql2005及以上版本才有。

引用MSDN：
可以使用 PIVOT 和 UNPIVOT 關係運算子將表值表示式更改為另一個表。PIVOT 通過將表示式某一列中的唯一值轉換為輸出中的多個列來旋轉表值表示式，並在必要時對最終輸出中所需的任何其餘列值執行聚合。UNPIVOT 與 PIVOT 執行相反的操作，將表值表示式的列轉換為列值，但是在實際應用中，有些聚合之後的資料很難進行拆分。所以呢，unpivot並非pivot的逆過程。

pivot 提供的語法比一系列複雜的 SELECT...CASE 語句中所指定的語法更簡單和更具可讀性。
使用SELECT...CASE語句進行行轉列的方法見：解析SQL Server中行轉列問題

簡單的例子如下：

---------行轉列
--建表
create table test(編號 int,姓名 varchar(20),季度 int,銷售額 int)
insert into test values(1,'simon',1,1000)
insert into test values(1,'simon',2,2000)
insert into test values(1,'simon',3,3000)
insert into test values(1,'simon',4,4000)
insert into test values(2,'meme',1,5000)
insert into test values(2,'meme',2,6000)
insert into test values(2,'meme',3,7000)
insert into test values(2,'meme',4,8000)

--執行普通查詢
select * from test

--執行轉換查詢
select 編號,姓名,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度",
[5] as "隨便1"
from
test
pivot
(
sum(銷售額)
for 季度 in
([1],[2],[3],[4],[5])
)
as pvt

--------列轉行
--建表
create table test2(編號 int,姓名 varchar(20), 一季度 int, 二季度 int, 三季度 int, 四季度 int)
insert into test2 values(1,'simon',1000,2000,4000,5000)
insert into test2 values(2,'meme',3000,3500,4200,5500)

--執行普通查詢
select * from test2

--執行轉換查詢
select 編號,姓名,季度,銷售額
from
test2
unpivot
(
銷售額
for 季度 in
(一季度,二季度,三季度,四季度)
)
as unpvt

建議：如果你想了解的更加清楚，請參考：http://technet.microsoft.com/zh-cn/library/ms177410.aspx
注意：對升級到 SQL Server 2005 或更高版本的資料庫使用 PIVOT 和 UNPIVOT 時，必須將資料庫的相容級別設定為 90 或更高。

有的SQL Server 2005初始安裝時，預設的相容級別為“80”，這時我們需要將相容級別進行設定，不然，PIVOT不能正常的執行。我在使用PIVOT時就遇到這樣的問題。
具體的修改方案如下：

修改相容級別步驟
1、連線到相應的 SQL Server 資料庫引擎例項之後，在物件資源管理器中，單擊伺服器名稱以展開伺服器樹。
2、展開“資料庫”，然後根據資料庫的不同，選擇使用者資料庫，或展開“系統資料庫”，再選擇系統資料庫。
3、右鍵單擊資料庫，再單擊“屬性”。
4、在“資料庫屬性”對話方塊中，單擊“選項”，將會看到“相容級別”列表框中。
5、若要更改相容級別，請從列表中選擇其他選項。
可用選項包括 SQL Server 2000 (80)、SQL Server 2005 (90) 或 SQL Server 2008 (100)。
具體的相容級別之間的差異請參考：http://technet.microsoft.com/zh-cn/library/bb510680.aspx

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-679376/，如需轉載，請註明出處，否則將追究法律責任。