[SQL]行转列和列转行

参考自：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
...