Oracle列转行

打印报表的时候可能需要数据格式的转换

select *
from 
(
select k.lev id,k.type||k.name||' '||k.num cname,k.ranknum from 
(
with temp as(
select '[甲类]' type ,'黄邸' name,'15g' num from dual union all
select '[甲类]' type ,'白术' name,'20g' num from dual union all
select '[甲类]' type ,'半夏' name,'30g' num from dual union all
select '[甲类]' type ,'当归' name,'15g' num from dual union all
select '[甲类]' type ,'红景天' name,'10g' num from dual union all
select '[甲类]' type ,'雪见草' name,'10g' num from dual union all
select '[甲类]' type ,'萱草' name,'15g' num from dual
)
select mod(rownum,3) ranknum,a.*,ceil(rownum/3) lev from temp a
) k) pivot(max(cname) for ranknum in (1 as 药品1,2 as 药品2,0 as 药品3));

结果如下图: