Oracle pivot in子查询的疑问

freestyleyooo
  • 16

在使用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'));

下图是原始的数据

clipboard.png

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

clipboard.png

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

clipboard.png

求大神指导

回复
阅读 17.4k
3 个回答

按照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动态执行。

Orange
  • 1
新手上路,请多包涵

请问楼主是怎么解决的 我也遇到了这个问题,在线急切等待中,谢谢

宣传栏