行转列:
源表:
case when:
select y,
sum(case when q=1 then amt end) q1,
sum(case when q=2 then amt end) q2,
sum(case when q=3 then amt end) q3,
sum(case when q=4 then amt end) q4
from test04 group by y;
效果:
pivot:
select * from test04
pivot(
sum(amt) for q in(1 as q1,2 as q2,3 as q3,4 as q4))
效果:
列转行:
unpivot:unpivot(新列名 for 聚合列名 in (对应的列名1…列名n ))
select * from (
select * from test04
pivot(
sum(amt) for q in(1 as q1,2 as q2,3 as q3,4 as q4))) --沿用上面行转列的基础上,进行列转行回来
unpivot
(amt for q in(q1,q2,q3,q4))
效果:
标签:case,q1,转行,when,转列,sum,oracle,amt,select From: https://www.cnblogs.com/stanzhou47/p/16753427.html