行转列函数:pivot(聚合函数(要转的列) for 要转的列 in ('值1' as "字段1", '值2' as "字段2") )
ps: 没啥好用的聚合函数的话,max也是爱
要转换的字段,用"双引号和大写",小写的话,如果用在with tmp 那样的东西上关联的时候有问题,所以建议直接大写
举个栗子
with tmp as(
select * from (select stu_no,sorce from stu_sorce)
pivot (max(sorce) for sorce in ('100' as "GOOD",'59' AS "BAD"))
)
select * From stu_info si
left join tmp on si.stu_no=si.stu_no
in函数里面不知道能不能用什么表达式,有空可以试一下,但基本用法就是这样的了