SQL Server 2005之PIVOT/UNPIVOT行列转换 

http://technet.microsoft.com/zh-cn/library/ms177410.aspx

 

创建测试表，插入测试数据

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将个季度的利润转成横向显示：

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