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;