用法说明:
Select * From 表名
PIVOT (
SUM('要合并的列1'),MAX('要合并的列2'),....FOR 将值转换成列的列名 IN (列值1,列值2,列值3,列值4....)
)
从用法中可以看出sum,max是配合PIVOT聚合函数,聚合函数必须要有。
实例1:
select * from t_kh_lxxx;
| LXFSBH | KHBH | LXFSLX | XH | LXFS |
1 | 11 | 2017112101 | 1 | 1 | 13812893231 |
2 | 12 | 2017112101 | 2 | 2 | 1111@qq.com |
3 | 13 | 2017112102 | 1 | 1 | 18812893231 |
4 | 14 | 2017112102 | 2 | 2 | 2222@qq.com |
5 | 15 | 2017112103 | 1 | 1 | 15812893231 |
6 | 16 | 2017112103 | 2 | 2 | 3333@qq.com |
7 | 17 | 2017112103 | 3 | 3 | 2323232 |
8 | 4 | 2017032702 | 5 | 2 | 0723-23283928 |
9 | 5 | 2017032702 | 4 | 2 | 18523828389 |
10 | 3 | 21591068 | 1 | 1 | 13829328892 |
11 | 6 | 8150 | 1 | 1 | 137985230398 |
12 | 7 | 8150 | 4 | 2 | 152238889931 |
13 | 8 | 8150 | 5 | 3 | 0734-985230398 |
14 | 9 | 8150 | 3 | 4 | wwew223 |
15 | 10 | 21591068 | 2 | 2 | 66768@qq.com |
16 | 2 | 2017032702 | 1 | 1 | 13631588507 |
17 | 18 | 2017032702 | 1 | 1 | 0723-23283929 |
18 | 1 | 25024368 | 1 | 1 | 137282932 |
19 | 19 | 2018010405 | 1 | 1 | 1581333333 |
使用pivot
个人理解:pivot会对查询的列自动分组,其中一个列需要将值转换为多列,聚合一个列为这个多列的值
实例2-1
--查询字段为khbh,lxfs,lxfslx
--聚合了lxfs
--把lxfslx转换成多列:如果lxfslx=1那么列名就为 手机号码
--这就相当于按khbh分组了
select * from ( select khbh,lxfs,lxfslx from t_kh_lxxx)
pivot (
max(lxfs) for lxfslx in (
1 as 手机号码,
2 as 家庭电话
)
)
查询结果:
| KHBH | 手机号码 | 家庭电话 |
1 | 8150 | 137985230398 | |
2 | 21591068 | 13829328892 | 66768@qq.com |
3 | 25024368 | 137282932 | |
4 | 2017032702 | 13631588507 | |
5 | 2017112101 | 13812893231 | 1111@qq.com |
6 | 2017112102 | 18812893231 | 2222@qq.com |
7 | 2017112103 | 15812893231 | 3333@qq.com |
8 | 2018010405 | 1581333333 |
实例2-2
--查询字段为khbh,lxfsbh,lxfs,lxfslx
--聚合了lxfs
--把lxfslx转换成多列:如果lxfslx=1那么列名就为 手机号码
--这就相当于按khbh,lxfsbh分组了
select * from ( select lxfsbh,khbh,lxfs,lxfslx from t_kh_lxxx)
pivot (
max(lxfs) for lxfslx in (
1 as 手机号码,
2 as 家庭电话
)
)
查询结果:
| LXFSBH | KHBH | 手机号码 | 家庭电话 |
1 | 10 | 21591068 | 66768@qq.com | |
2 | 19 | 2018010405 | 1581333333 | |
3 | 14 | 2017112102 | 2222@qq.com | |
4 | 8 | 8150 | ||
5 | 4 | 2017032702 | ||
6 | 7 | 8150 | ||
7 | 18 | 2017032702 | 0723-23283929 | |
8 | 1 | 25024368 | 137282932 | |
9 | 13 | 2017112102 | 18812893231 | |
10 | 2 | 2017032702 | 13631588507 | |
11 | 12 | 2017112101 | 1111@qq.com | |
12 | 15 | 2017112103 | 15812893231 | |
13 | 3 | 21591068 | 13829328892 | |
14 | 9 | 8150 | ||
15 | 16 | 2017112103 | 3333@qq.com | |
16 | 5 | 2017032702 | ||
17 | 6 | 8150 | 137985230398 | |
18 | 11 | 2017112101 | 13812893231 | |
19 | 17 | 2017112103 |