Oracle 动态SQL实现SQL查询子集行转列

引言

我们都知道Oracle的Pivot的In（）里是不能跟SQL查询语句的，必须是已知的字符串结果，然而我项目中遇到的需求这个In 并不是静态的，而是根据我传递的条件来筛选，一开始想的是解析XML，可惜转为XML之后，有相同的重复的节点，解析太麻烦，于是想通过动态sql，先把SQl查询结果再另一个函数里查询出来，再赋值给一个中间变量，作为参数传递到动态SQL里。

Pivot相关

在 Oracle 数据库 11g 推出之前，您需要针对每个值通过 decode 函数进行以上操作，并将每个不同的值编写为一个单独的列。但是，该方法一点也不直观。 庆幸的是，您现在可以使用一种很棒的新特性 PIVOT 通过一种新的操作符以交叉表格式显示任何查询，该操作符相应地称为 pivot。下面是查询的编写方式：

select * from (
   select times_purchased, state_code
   from customers t
)
pivot 
(
   count(state_code)
   for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased

执行结果

输出如下：
. TIMES_PURCHASED       'NY'       'CT'       'NJ'       'FL'       'MO'
--------------- ---------- ---------- ---------- ---------- ----------
              0      16601         90          0          0          0
              1      33048        165          0          0          0
              2      33151        179          0          0          0
              3      32978        173          0          0          0
              4      33109        173          0          1          0

这表明了 pivot 操作符的威力。state_codes 作为标题行而不是列显示。下面是传统的表格化格式的图示：

动态SQL实现SQL查询子集行转列

CREATE OR REPLACE PROCEDURE p_Getgatherreprot37102(p_Provinceno IN VARCHAR2,
                                                   p_Cityno     IN VARCHAR2,
                                                   p_Countryno  IN VARCHAR2,
                                                   Ref_Cursor   OUT SYS_REFCURSOR --返回的结果集，游标
                                                   ) AS
  v_Getstrulvlsql VARCHAR(4000) := '';
  v_Getsql        VARCHAR(4000) := '';
  v_columnNames Varchar2(32767) :='';
  /*获取烟叶等级结构的信息并行转列*/
BEGIN
--获取查询并拼接成一定格式的字符串 作为参数传递到动态SQL里
  SELECT f_Concat_Name(p_Provinceno,p_Cityno,p_Countryno) INTO v_Getstrulvlsql FROM dual t;
  v_Getsql := '--Open ref_cursor for
          SELECT *
          FROM   (SELECT DISTINCT Cl.Levelno, Cl.Levelqualifiedratio
                  FROM   Pdm_Ylzj_Crutoqualinspe c
                  LEFT   JOIN Pdm_Ylzj_Crutosendinspe Sj
                  ON     c.Pdm_Ylzj_Crutosendinspeid = Sj.Pdm_Ylzj_Crutosendinspeid
                  LEFT   JOIN (SELECT X1.Pdm_Ylzj_Crutosendinspeid,
                                     MAX(X1.Pdm_Ylzj_Crutoreporinspeid) AS Pdm_Ylzj_Crutoreporinspeid
                              FROM   Pdm_Ylzj_Crutosendinsped X1
                              INNER  JOIN Pdm_Ylzj_Crutoreporinspe X2
                              ON     X1.Pdm_Ylzj_Crutoreporinspeid = X2.Pdm_Ylzj_Crutoreporinspeid
                              WHERE  X2.State = 2
                              GROUP  BY X1.Pdm_Ylzj_Crutosendinspeid) f
                  ON     Sj.Pdm_Ylzj_Crutosendinspeid = f.Pdm_Ylzj_Crutosendinspeid
                  INNER  JOIN Pdm_Ylzj_Crutolevelstruc Cl';

         v_Getsql:=v_Getsql||'  ON Cl.Pdm_Ylzj_Crutoqualinspeid = c.Pdm_Ylzj_Crutosendinspeid';

         v_Getsql:=v_Getsql||' LEFT JOIN Pdm_Ylzj_Recrutoqualinspe Fj
                  ON  Fj.Inspectionno = c.Inspectionno AND Nvl(Fj.Isdeleted, ''0'') = ''0''
                  WHERE  Nvl(c.Isdeleted, 0) = 0 AND f.Pdm_Ylzj_Crutosendinspeid IS NULL AND
                         Fj.Pdm_Ylzj_Recrutoqualinspeid IS NULL AND Cl.Leveltype = ''1'' OR
                         Cl.Leveltype = ''2''
                         AND c.Provincesno = ''HB'' AND c.Cityno = ''ES'' AND c.Countyno = ''ES'')
          Pivot(SUM(Levelqualifiedratio)';
  v_Getsql:= v_Getsql || ' FOR  Levelno IN('||v_Getstrulvlsql||'));';
  execute Immediate v_Getsql Into Ref_Cursor;

END;