pivot 与 unpivot函数
pivot 与 unpivot函数
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
------------------------------------------------------------------------------
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 --创建表test
现在需要把quarter 从1列数据变成4列数据 效果如：
把一列拆成几列这时候就能使用pivot函数很简单的实现
select * from test
pivot
(
sum([profile]) for [quarter]
in
([1],[2],[3],[4])
)
as
s
注：使用pivot把一列拆成几列时 需要后面as取个别名 这是固定的格式 同时如 for前是必须使用聚合函数的
当然不使用pivot函数也可以得到相同效果 只是代码长切效率低 但容易理解
select id,[name],
'1'=(select sum([profile]) from test where id=a.id and quarter=1),
'2'=(select sum([profile]) from test where id=a.id and quarter=2),
'3'=(select sum([profile]) from test where id=a.id and quarter=3),
'4'=(select sum([profile]) from test where id=a.id and quarter=4)
from test as a
group by id,name
-----------------------------------------------------------------------------------------
unpivot函数 顾名思义 他就是把几列合并到1列中去
create table test1(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test1 values(1,'a',1000,2000,4000,5000)
insert into test1 values(2,'b',3000,3500,4200,5500)
select * from test1 --创建test1表
我们要把Q1 Q2 Q3 Q4合到1列 季度列中去 如效果：
使用unpivot可以很简单的实现
select id ,[name],[jidu],[xiaoshou] from test1
unpivot
(
xiaoshou for jidu in
([q1],[q2],[q3],[q4])
)
as f
注：同样需要使用as取别名同样是固定的格式 unpivot函数中没有聚合函数 xiaoshou和jidu列都是原来没有的 jidu表由原来的Q1 Q2 Q3 Q4组成
SQL UNPIVOT 多列
先準備測試資料如下，
create table T (
no varchar(10),
yearx varchar(4),
z_type varchar(1),
A01 smallint,
B01 smallint,
A02 smallint,
B02 smallint,
A03 smallint,
B03 smallint
)
go
insert into T VALUES('Z01','2012','1',100,-10,200,30,50,60);
insert into T VALUES('Z01','2012','3',200,20,100,20,30,40);
insert into T VALUES('Z01','2013','3',300,30,10,50,70,30);
SELECT * FROM T;
因為原本欄位A01, A02, A03要放到欄位A，B01, B02, B03要放到欄位B，所以需要2個UNPIVOT，如下，
SELECT no, yearx, z_type, A, B, x1, x2
FROM
(SELECT *
FROM T) p
UNPIVOT ( A FOR x1 IN (A01, A02, A03) )AS UnA
UNPIVOT ( B FOR x2 IN (B01, B02, B03) )AS UnB
上面的輸出筆數為27筆，而我們可以發現，x1, x2欄位中，A01 搭配 B01，A02 要搭配 B02，A03 要搭配 B03，如下圖所示，
所以我們需要再加入 關鍵的 WHERE 條件， RIGHT(x1, 1) = RIGHT(x2, 1)，就可以過濾出我們所需要的資料，如下，
SELECT no, yearx, z_type, A, B, x1, x2
FROM
(SELECT *
FROM T) p
UNPIVOT ( A FOR x1 IN (A01, A02, A03) )AS UnA
UNPIVOT ( B FOR x2 IN (B01, B02, B03) )AS UnB
WHERE substr(x1, -1,1) = substr(x2, -1,1)
參考資料
