if object_id('test') is not null
drop table testcreate 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 testselect * from test
pivot
(
sum(profile) for quarter in([1],[2],[3],[4])
) t
(1 行受影响)
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 行受影响)id name 1 2 3 4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500(2 行受影响)
我不明白 这里应该是隐式的group by的如果有多个字段的话 pivot 倒底是按哪个字段 group by 的呢
select * from test
drop table testcreate 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 testselect * from test
pivot
(
sum(profile) for quarter in([1],[2],[3],[4])
) t
(1 行受影响)
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 行受影响)id name 1 2 3 4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500(2 行受影响)
我不明白 这里应该是隐式的group by的如果有多个字段的话 pivot 倒底是按哪个字段 group by 的呢
select * from test
if object_id('test') is not null
drop table testcreate 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(0,'b',2,3500)
insert into test values(0,'b',3,4200)
insert into test values(0,'b',4,1000)select * from test
pivot
(
sum(profile) for quarter in([1],[2],[3],[4])
) tid name 1 2 3 4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
0 b NULL 3500 4200 1000
2 b 3000 NULL NULL NULL(3 行受影响)
goif object_id('test') is not null
drop table testcreate table test(id int,name varchar(20),quarter int,profile int) insert into test values(1,'a',1,1000)
insert into test values(1,'c',2,2000)
insert into test values(1,'c',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'d',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'d',4,5500)
select * from test
pivot
(
sum(profile) for quarter in([1],[2],[3],[4])
) t/*
id name 1 2 3 4
---------------------------------------------------
1 a 1000 NULL NULL 5000
2 b 3000 NULL 4200 NULL
1 c NULL 2000 4000 NULL
2 d NULL 3500 NULL 5500
*/'由此可以看出 pivot 查询语句是对除 pivot 里面字段以外的(多个)字段进行 group'
if object_id('test') is not null
drop table test
go
create table test(id int,name varchar(20),name2 varchar(20),quarter int,profile int)
insert into test values(1,'a','a',1,1000)
insert into test values(1,'a','b',2,2000)
insert into test values(1,'a','c',3,4000)
insert into test values(1,'a','d',4,5000)
insert into test values(2,'b','q',1,3000)
insert into test values(2,'b','q',2,3500)
insert into test values(2,'b','q',3,4200)
insert into test values(2,'b','q',4,5500)select * from testselect * from test
pivot
(
sum(profile) for quarter in([1],[2],[3],[4])
) t
/*id name name2 1 2 3 4
----------- -------------------- -------------------- ----------- ----------- ----------- -----------
1 a a 1000 NULL NULL NULL
1 a b NULL 2000 NULL NULL
1 a c NULL NULL 4000 NULL
1 a d NULL NULL NULL 5000
2 b q 3000 3500 4200 5500(5 行受影响)
*/
假设你的表A 字段有 col1 col2 col3 col4
那么select * from a pivot (sum(coL3) for col4 in([1],[2],[3])) k这个语句中的 A 你可以作为输入表看待
再看PIVOT里面的字段 COL3 COL4 一个进行聚合 一个进行目标列筛选
那么A表剩下的COL1 COL2字段就作为分组列
如果你觉得 难理解 推荐你使用 聚合+case when +group by 因为2者效率几乎没差别反倒是UNPIVOT 可以多用。
和pivot没有太大的性能差别