[SQL]行转列和列转行

参考自：https://www.cnblogs.com/linJie1930906722/p/6036714.html

1、测试数据集

 usernamesubjectscore1张三语文802张三数学903张三生物854李四语文805李四数学 80...   

2、行转列

select * from student_score as P PIVOT ( sum(score) for p.subject IN ([语文],[数学],[英语],[生物]) ) as T

执行结果：

 username语文数学英语生物1张三8090 852李四8080  

3、列转行

测试数据：

 projectnameoverseasupplynativesupplysouthsupplynorthsupply1

A

10020050502B2003001501503C159400203204D250301515 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  projectnamesuppliersupplynum1Aoverseasupply1002Anativesupply2003Asouthsupply504Anorthsupply505Boverseasupply2006Bnativesupply300...