sql - Oracle pivot in子查询的疑问

问题描述

问 题

在使用pivot函数进行行转列的时候 in后面接子查询就报错

这是我的代码:

select *
from (select pscode,

   outputcode,
   pollutantcode,
   monitortime,
   AVGSTRENGTH
      from WATER_GX_OUTPUTFAC_DAYDATA
     where pscode = '430482000045'
       and outputcode = '201'
       and monitortime =
           to_date('2009/8/10 0:00:06', 'yyyy-mm-dd hh24:mi:ss')) pivot(sum(AVGSTRENGTH) for pollutantcode IN (select distinct pollutantcode

from WATER_GX_OUTPUTFAC_DAYDATA w
where pscode = '430482000045'
and outputcode = '201'));

下图是原始的数据

下图是in里面的子查询的结果

下图是我想要达到的效果,换成固定值就可以

求大神指导

解决方案

按照oracle的文档，pivot语句中in后面的列如果不固定，只能使用xml格式的返回结果，如：
http://www.oracle-developer.n...

SQL> SELECT *
  2  FROM   pivot_data
  3  PIVOT  XML
  4        (SUM(sal) FOR deptno IN (ANY));

JOB       DEPTNO_XML
--------- ---------------------------------------------------------------------------
ANALYST   <PivotSet><item><column name = "DEPTNO">20</column><column name = "SUM(SAL)
          ">6600</column></item></PivotSet>

CLERK     <PivotSet><item><column name = "DEPTNO">10</column><column name = "SUM(SAL)
          ">1430</column></item><item><column name = "DEPTNO">20</column><column name
           = "SUM(SAL)">2090</column></item><item><column name = "DEPTNO">30</column>
          <column name = "SUM(SAL)">1045</column></item></PivotSet>

MANAGER   <PivotSet><item><column name = "DEPTNO">10</column><column name = "SUM(SAL)
          ">2695</column></item><item><column name = "DEPTNO">20</column><column name
           = "SUM(SAL)">3272.5</column></item><item><column name = "DEPTNO">30</colum
          n><column name = "SUM(SAL)">3135</column></item></PivotSet>

如果不想要xml格式的结果，就只能使用把in之间的内容提前计算出来，动态拼接成sql语句，使用execute immediately动态执行。

这篇关于sql - Oracle pivot in子查询的疑问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！