行变列，列变行在生成报表的时候经常遇到，行变列叫做"Pivot”, 反之叫做"Unpivot”。 在Oracle11g之前，一般都是通过case来实现，但是Oracle11g及其以后直接支持PIVOT和UNPIVOT的操作。

pivot

语法：

SELECT * FROM （元数据） PIVOT  (  SUM(Score/*行转列后 列的值*/) FOR   coursename/*需要行转列的列*/ IN (转换后列的值) ) for 可以看成循环，for前是行转列后显示的值(这里必须使用聚合函数)，for后是循环的字段(一般是有限的固定值，如字典等) in (里是对for后的循环字段处理，比如起别名，做运算等)

SELECT
        to_char(to_date(t1.tjrq,yyyymmdd),yyyy"年"mm"月"dd"日") tjrq,
        nvl ( t1.ztrs, 0 ) yjjj,
        nvl ( t2.ztrs, 0 ) qyxf,
        nvl ( t3.ztrs, 0 ) dgmhs
from
    (SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM                                 
    A GROUP BY zrq, sjly, tjrq )
    where tjrq BETWEEN 20220814 and 20220914 and sjly=页面 GROUP BY sjly,tjrq) t1 
left join
    (SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM A         
     GROUP BY zrq, sjly, tjrq )
     where tjrq BETWEEN 20220814 and 20220914 and sjly=接口 GROUP BY sjly,tjrq) t2 on         
     t1.tjrq=t2.tjrq 
left join
     (SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM A     
     GROUP BY zrq, sjly, tjrq )
     where tjrq BETWEEN 20220814 and 20220914 and sjly=导入 GROUP BY sjly,tjrq) t3 on         
     t2.tjrq=t3.tjrq
order by t1.tjrq desc

虽说结果也是正确的，但无疑不是最优方案

使用pivot改造

select 
    to_char(to_date(tjrq,yyyymmdd),yyyy"年"mm"月"dd"日") tjrq,
    nvl ( yjjj, 0 ) yjjj,
    nvl ( qyxf, 0 ) qyxf,
    nvl ( dgmhs, 0 ) dgmhs
from 
(
	select sjly,tjrq,sum(ztrs) ztrs from (SELECT zrq, sjly,tjrq, min( ztrs ) ztrs FROM         
    A GROUP BY zrq, sjly ,tjrq) group by sjly,tjrq
) 
pivot (max(ztrs) for sjly in (页面 as yjjj,接口 as qyxf,导入 as dgmhs))
where tjrq BETWEEN 20220814 and 20220914
order by tjrq desc

教程例子：贴图助理解

 unpivot

列转行，就是将一行结果拆成多行显示

语法：

select 字段 from 数据集 unpivot（自定义列名/*列的值*/ for 自定义列名 in（列名））

如上图

可以使用union 或者union all