参考自:https://www.cnblogs.com/linJie1930906722/p/6036714.html
1、测试数据集
username | subject | score | |
1 | 张三 | 语文 | 80 |
2 | 张三 | 数学 | 90 |
3 | 张三 | 生物 | 85 |
4 | 李四 | 语文 | 80 |
5 | 李四 | 数学 | 80 |
... |
2、行转列
select * from student_score
as P
PIVOT
(
sum(score) for
p.subject IN ([语文],[数学],[英语],[生物])
) as T
执行结果:
username | 语文 | 数学 | 英语 | 生物 | |
1 | 张三 | 80 | 90 | 85 | |
2 | 李四 | 80 | 80 |
3、列转行
测试数据:
projectname | overseasupply | nativesupply | southsupply | northsupply | |
1 | A | 100 | 200 | 50 | 50 |
2 | B | 200 | 300 | 150 | 150 |
3 | C | 159 | 400 | 20 | 320 |
4 | D | 250 | 30 | 15 | 15 |
SELECT P.projectname,p.supplier,p.supplynum
FROM
(
select
projectname
,overseasupply
,nativesupply
,southsupply
,northsupply
from projectdetail
)T
UNPIVOT
(
supplynum for supplier IN
(overseasupply,nativesupply,southsupply,northsupply)
)P
projectname | supplier | supplynum | |
1 | A | overseasupply | 100 |
2 | A | nativesupply | 200 |
3 | A | southsupply | 50 |
4 | A | northsupply | 50 |
5 | B | overseasupply | 200 |
6 | B | nativesupply | 300 |
... |