oracle的文档 http://www.oracle-developer.net/display.php?id=506
参考文档:https://blog.csdn.net/alen_liu_sz/article/details/77326140
按照oracle的文档,pivot语句中in后面的列如果不固定,只能使用xml格式的返回结果,xml 类型的时候可以使用any 关键字和子查询,返回的结果是xml结构的;如:
select *
from (select sal, deptno, job from SCOTT.emp) pivot xml(sum(sal) for deptno in (SELECT DISTINCT deptno
FROM SCOTT.emp));
eg:第一行:job:ANALYST,deptno_xml如下
<PivotSet><item><column name = "DEPTNO">10</column><column name = "SUM(SAL)"></column></item><item><column name = "DEPTNO">20</column><column name = "SUM(SAL)">6000</column></item><item><column name = "DEPTNO">30</column><column name = "SUM(SAL)"></column></item></PivotSet>
使用any关键字:
SELECT * FROM (select sal, deptno, job from SCOTT.emp) PIVOT XML(SUM(sal) FOR deptno IN (ANY));
eg:第一行:job:ANALYST,deptno_xml如下
<PivotSet><item><column name = "DEPTNO">20</column><column name = "SUM(SAL)">6000</column></item></PivotSet>
如果不想要xml格式的结果,就只能使用把in之间的内容提前计算出来,动态拼接成sql语句,使用execute immediately动态执行。