DB2中的动态透视SQL查询 [英] dynamic pivot SQL Query in DB2

问题描述

我在DB2中有类似下面的表，其中这3列都是VARCHAR数据类型

DATE_F    FILE_NM   ROW_ID  COLUMN_NM   COLUMN_VAL 
20200131  ABC.TXT     1      ABC_COL1     123
20200131  ABC.TXT     1      ABC_COL2     XYZ
20201231  ABC.TXT     2      ABC_COL1     456
20201231  ABC.TXT     2      ABC_COL2     XY1
20200630  XYZ.TXT     1      XYZ_COL1     PQR
20200630  XYZ.TXT     1      XYZ_COL2     567
20200630  XYZ.TXT     1      XYZ_COL3     MNO

我需要一个动态透视查询，当FILE_NM筛选器用作ABC.TXT

时，它应该以行格式选择ABC_COL1和ABC_COL2的值

当FILE_NM筛选器用作XYZ.TXT时，同一查询应以行格式选择XYZ_COL1、XYZ_COL2和XYZ_COL3的值

当FILE_NM=ABC.TXT时输出应如下所示

DATE_F    ROW_ID   ABC_COL1   ABC_COL2    
20200131    1        123       XYZ
20201231    2        456       XY1

当FILE_NM=XYZ.TXT时，输出应如下所示

 DATE_F    ROW_ID     XYZ_COL1     XYZ_COL2     XYZ_COL3
20200630    1           PQR         567           MNO

因此，一个查询将能够提取数据。在这种情况下，是否有任何函数或等效函数可以执行此任务？

推荐答案

您可以尝试使用以下泛型存储过程进行透视。

--#SET TERMINATOR @
create or replace procedure pivot
(
  in  sel_stmt varchar(4000)
, in  row_cols varchar(200)
, in  col_col  varchar(128)
, in  agg_col  varchar(128)
, in  agg_fn   varchar(10)
, in  tmp_tbl  varchar(128)
, in  null_ind varchar(10)
, out rc       int
, out msg      varchar(128)
, out stmt     varchar(4000)
)
LANGUAGE SQL
DYNAMIC RESULT sets 1
BEGIN
 declare QUOT1        char(1) default '''';
 declare QUOT2        char(1) default '"';
 declare SQLCODE      int default 0;
 declare SQLTYPE_ID   int;
 declare SQLTYPE      varchar(128);
 declare SQLLENGTH    int;
 declare SQLSCALE     int;
 declare SQLNAME_DATA varchar(128);
 declare SQLTYPEF     varchar(128);
 declare col_val      varchar(4000);
 declare apo          varchar(1);
 declare l1 RESULT_set_LOCATOR VARYING;

 declare c2 cursor for s2;
 declare c_out cursor with return for s_out;

 declare EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
   GET DIAGNOSTICS EXCEPTION 1 MSG = MESSAGE_TEXT;
   set RC = SQLCODE;
 END;

 set col_col=upper(col_col);
 set agg_col=upper(agg_col);
 -- insert result of select statement into temp table
 set stmt = 'describe '||sel_stmt;
 call SYSPROC.ADMIN_CMD(stmt);
 set stmt = '';
 associate result set locator (l1)
 with procedure SYSPROC.ADMIN_CMD;
 allocate c1 cursor for result set l1;
 --open c1;
 fetch c1 into SQLTYPE_ID, SQLTYPE, SQLLENGTH, SQLSCALE, SQLNAME_DATA;
 while (SQLCODE!=100) do
   set SQLTYPEF = SQLTYPE
   ||case 
      when SQLTYPE IN ('DECIMAL', 'DECFLOAT', 'CHARACTER', 'VARCHAR') then 
         '('||RTRIM(CHAR(SQLLENGTH))
       ||case when SQLTYPE='DECIMAL' then ','||RTRIM(CHAR(SQLSCALE)) else '' end
       ||')'                                
      else ''
     end;
   if (col_col=SQLNAME_DATA) then
     set apo = 
      case 
       when SQLTYPE in ('DECIMAL', 'DECFLOAT', 'INTEGER', 'SMALLINT', 'BIGINT', 'REAL', 'DOUBLE') then '' 
       else QUOT1 
      end;
   end if;
   set stmt = stmt||', '||SQLNAME_DATA||' '||SQLTYPEF;
   fetch c1 into SQLTYPE_ID, SQLTYPE, SQLLENGTH, SQLSCALE, SQLNAME_DATA;
 end while;
 close c1;
 set stmt = 
   'declare global temporary table '||tmp_tbl||'('||substr(stmt, 3)
 ||') with replace on commit preserve rows not logged';
 execute immediate stmt;
 set stmt = 'insert into '||tmp_tbl||' '||sel_stmt;
 execute immediate stmt;

 -- construct select statement
 set stmt = 'select distinct rtrim(char('||col_col||')) from '||tmp_tbl||' order by 1';
 prepare s2 from stmt;
 set stmt='';
 open c2;
 fetch c2 into col_val;
 while (SQLCODE!=100) do
   set stmt = 
     stmt||', '||agg_fn||'('
   ||'case when '||col_col||' '
   ||case when col_val is null then 'IS NULL' else ('='||apo||replace(col_val, QUOT1, QUOT1||QUOT1)||apo) end
   ||' then '||agg_col||' end) as '||QUOT2||coalesce(replace(col_val, QUOT2, QUOT2||QUOT2), null_ind)||QUOT2;
   fetch c2 into col_val;
 end while;
 close c2;
 -- add to the select statement groups
 set row_cols = nullif(row_cols, '');
 set stmt = 
   'select '||case when row_cols is not null then row_cols||',' else coalesce(row_cols, '') end
 ||substr(stmt, 2)||' from '||tmp_tbl||' '
 ||case when row_cols is not null then ('group by '||row_cols||' order by '||row_cols) else '' end;
 -- execute this statement
 prepare s_out from stmt;
 open c_out;
END@

参数说明：

参数	设计
sel_stmt	用于生成源数据的任何有效SELECT语句
行数	最后一条语句的GROUP BY中使用的列名的逗号分隔列表
列_列	要透视的列名
AGG_COOL	要聚合的列名
agg_fn	参数中列名的任何有效的DB2聚合函数
tmp_tbl	中间结果的DGTT名称
NULL_ind	空指示符
rc	返回代码(OUT)
消息	消息文本(Out)
stmt	最终选择已生成(输出)

简介：
将创建传入tmp_tbl的DGTT，并使用传入的sel_stmtSELECT语句的结果进行填充。row_cols、col_col、agg_col中指定的所有列名都必须在此语句的选择列表中。
此DGTT的最后一条SELECT语句是使用以下规则动态生成的：

• 对于col_col传入的列名中每个不同的值Vx，生成一个额外的列表达式，如下所示：, <agg_fn> (case when <col_col> = Vx then <agg_col> end) as "Vx"(, <agg_fn> (case when <col_col> IS NULL then <agg_col> end) as "<null_ind>"用于Vx为空)，其中<parameter>表示该参数传递的字符串值。
• row_cols列列表用于GROUP BY，如果它不为空。

用于您的案例：

DECLARE GLOBAL TEMPORARY TABLE SESSION.MYTAB 
(DATE_F, FILE_NM, ROW_ID, COLUMN_NM, COLUMN_VAL)
AS
(
VALUES
  ('20200131', 'ABC.TXT', 1, 'ABC_COL1', '123')
, ('20200131', 'ABC.TXT', 1, 'ABC_COL2', 'XYZ')
, ('20201231', 'ABC.TXT', 2, 'ABC_COL1', '456')
, ('20201231', 'ABC.TXT', 2, 'ABC_COL2', 'XY1')
, ('20200630', 'XYZ.TXT', 1, 'XYZ_COL1', 'PQR')
, ('20200630', 'XYZ.TXT', 1, 'XYZ_COL2', '567')
, ('20200630', 'XYZ.TXT', 1, 'XYZ_COL3', 'MNO')
) WITH DATA WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

第一次调用：

call pivot
(
  'select * from session.mytab where file_nm = ''ABC.TXT'''
, 'DATE_F, ROW_ID'
, 'COLUMN_NM'
, 'COLUMN_VAL'
, 'max'
, 'session.tab1'
, '-'
, ?, ?, ?
);

DATE_F	ROW_ID	abc_col1	ABC_COL2
20200131	1	123	XYZ
20201231	2	456	XY1

第二次呼叫：

call pivot
(
  'select * from session.mytab where file_nm = ''XYZ.TXT'''
, 'DATE_F, ROW_ID'
, 'COLUMN_NM'
, 'COLUMN_VAL'
, 'max'
, 'session.tab1'
, '-'
, ?, ?, ?
);

DATE_F	ROW_ID	XYZ_COL1	XYZ_COL2	XYZ_COL3
20200630	1	PQR	567	MNO

这篇关于DB2中的动态透视SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！