SQL Server 2005之PIVOT/UNPIVOT行列轉(zhuǎn)換
http://technet.microsoft.com/zh-cn/library/ms177410.aspx
創(chuàng)建測試表，插入測試數(shù)據(jù)
SQL code
createtable test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select*from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
利用PIVOT將個季度的利潤轉(zhuǎn)成橫向顯示：
select id,name,
[1]as "一季度",
[2]as "二季度",
[3]as "三季度",
[4]as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
