SQL Server2005的PIVOT/UNPIVOT行列转

/* SQL Server2005的PIVOT/UNPIVOT行列转。

SQL Server2005引入了很多迎合开发者口味的新特性，虽然改动不大，却大大了减少了开发者的工作量。
在SQL Server2000中，要实现行列转换，需要综合利用聚合函数和动态SQL，实现起来需要一定的技巧，
到了SQL Server2005中，使用新引进的关键字PIVOT/UNPIVOT，可以轻松实现行列转换的需求。

下面以两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。 */


/* PIVOT */

/* 创建测试表，插入测试数据 */
create table 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

(2 row(s) affected)
*/


/* UNPIVOT */

/* 建立测试表，插入测试数据 */
drop table test

create table test(id int ,name varchar ( 20 ), Q1 int , Q2 int , Q3 int , Q4 int )

insert into test values ( 1 , ' a ' , 1000 , 2000 , 4000 , 5000 )
insert into test values ( 2 , ' b ' , 3000 , 3500 , 4200 , 5500 )


select * from test
/*
id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

(2 row(s) affected)
*/

/* 利用UNPIVOT，将同一行中四个季度的列数据转换成四行数据： */
select id,name,quarter,profile
from
test
unpivot
    (
    profile
    for quarter in
    ( [ Q1 ] , [ Q2 ] , [ Q3 ] , [ Q4 ] )
    )
    as unpvt
/*
id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500

(8 row(s) affected)
*/