PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。
1、PIVOT 使用实例
先建立一个临时表做为例表:
1
create
table
#PIVOT (id
int
,name
varchar
(
50
),
[
subject
]
varchar
(
50
),val
decimal
(
18
,
2
))
2
3 INSERT INTO #PIVOT VALUES ( 1 , ' 张三 ' , ' 语文 ' , 54.12 );
4 INSERT INTO #PIVOT VALUES ( 2 , ' 张三 ' , ' 数学 ' , 25.3 );
5 INSERT INTO #PIVOT VALUES ( 3 , ' 张三 ' , ' 外语 ' , 69 );
6 INSERT INTO #PIVOT VALUES ( 4 , ' 张三 ' , ' 物理 ' , 15.23 );
7 INSERT INTO #PIVOT VALUES ( 5 , ' 张三 ' , ' 化学 ' , 15.02 );
8
9 INSERT INTO #PIVOT VALUES ( 6 , ' 李四 ' , ' 语文 ' , 54.12 );
10 INSERT INTO #PIVOT VALUES ( 7 , ' 李四 ' , ' 数学 ' , 25.3 );
11 INSERT INTO #PIVOT VALUES ( 8 , ' 李四 ' , ' 外语 ' , 69 );
12 INSERT INTO #PIVOT VALUES ( 9 , ' 李四 ' , ' 物理 ' , 15.23 );
13 INSERT INTO #PIVOT VALUES ( 10 , ' 李四 ' , ' 化学 ' , 15.02 );
2
3 INSERT INTO #PIVOT VALUES ( 1 , ' 张三 ' , ' 语文 ' , 54.12 );
4 INSERT INTO #PIVOT VALUES ( 2 , ' 张三 ' , ' 数学 ' , 25.3 );
5 INSERT INTO #PIVOT VALUES ( 3 , ' 张三 ' , ' 外语 ' , 69 );
6 INSERT INTO #PIVOT VALUES ( 4 , ' 张三 ' , ' 物理 ' , 15.23 );
7 INSERT INTO #PIVOT VALUES ( 5 , ' 张三 ' , ' 化学 ' , 15.02 );
8
9 INSERT INTO #PIVOT VALUES ( 6 , ' 李四 ' , ' 语文 ' , 54.12 );
10 INSERT INTO #PIVOT VALUES ( 7 , ' 李四 ' , ' 数学 ' , 25.3 );
11 INSERT INTO #PIVOT VALUES ( 8 , ' 李四 ' , ' 外语 ' , 69 );
12 INSERT INTO #PIVOT VALUES ( 9 , ' 李四 ' , ' 物理 ' , 15.23 );
13 INSERT INTO #PIVOT VALUES ( 10 , ' 李四 ' , ' 化学 ' , 15.02 );
表的内容:
使用PIVOT 语句:
1
select
[
subject
]
,
[
张三
]
,
[
李四
]
from
(
2 select [ subject ] ,name,val from #PIVOT
3 ) as p1
4 pivot ( max (val) for name in ( [ 张三 ] , [ 李四 ] )) as p2
5 order by p2. [ subject ]
2 select [ subject ] ,name,val from #PIVOT
3 ) as p1
4 pivot ( max (val) for name in ( [ 张三 ] , [ 李四 ] )) as p2
5 order by p2. [ subject ]
运行结果:
2、UNPIVOT实例
建立一个临时表结构:
1
CREATE
TABLE
#pvt (VendorID
int
, Emp1
int
, Emp2
int
,
2 Emp3 int , Emp4 int , Emp5 int );
3
4 INSERT INTO #pvt VALUES ( 1 , 4 , 3 , 5 , 4 , 4 );
5 INSERT INTO #pvt VALUES ( 2 , 4 , 1 , 5 , 5 , 5 );
6 INSERT INTO #pvt VALUES ( 3 , 4 , 3 , 5 , 4 , 4 );
7 INSERT INTO #pvt VALUES ( 4 , 4 , 2 , 5 , 5 , 4 );
8 INSERT INTO #pvt VALUES ( 5 , 5 , 1 , 5 , 5 , 5 );
2 Emp3 int , Emp4 int , Emp5 int );
3
4 INSERT INTO #pvt VALUES ( 1 , 4 , 3 , 5 , 4 , 4 );
5 INSERT INTO #pvt VALUES ( 2 , 4 , 1 , 5 , 5 , 5 );
6 INSERT INTO #pvt VALUES ( 3 , 4 , 3 , 5 , 4 , 4 );
7 INSERT INTO #pvt VALUES ( 4 , 4 , 2 , 5 , 5 , 4 );
8 INSERT INTO #pvt VALUES ( 5 , 5 , 1 , 5 , 5 , 5 );
表内容:
UNPIVOT语句:
1
SELECT
VendorID, Employee, Orders
2 FROM
3 ( SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
4 FROM #pvt) p
5 UNPIVOT
6 (Orders FOR Employee IN
7 (Emp1, Emp2, Emp3, Emp4, Emp5)
8 ) AS ss;
2 FROM
3 ( SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
4 FROM #pvt) p
5 UNPIVOT
6 (Orders FOR Employee IN
7 (Emp1, Emp2, Emp3, Emp4, Emp5)
8 ) AS ss;
运行结果: