db2 实现oracle wm_concat函数的方法-db2开发系列（一）

引言

 

大家在做oracle开发的时候，都知道有一个函数wm_concat ，它可以根据某一字段分组 把其他字段多行数据转1列 ，即分组的字段 字段1字段2...等等一条数据。最近db2里面有个统计某个菜单点击量的需求，要求把点击次数最多以及工号显示出来，当点击次数相同时，需要把那些工号用逗号链接显示在一行。还有给定任意表名 批量获得( select 所有字段from 表名 )的语句 （非*）这两种都需要这个函数。

 

实现

 

1 原始数据

 

SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name
                      ,CASE
                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'
                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'
                          ELSE colname
                       END AS colname
                  FROM SYSCAT.COLUMNS
                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =
                          lower ('xx.xxxxx')
                ORDER BY COLNO

 

原始数据本身是个查询是从 系统SYSCAT.COLUMNS 字典里取模式名.表名 和where 条件匹配上的 表的小写全名 和 所有的字段名。其中字符型和日期字段做了下处理，查询

结果如下：第一列 表名，第二列 字段名

 

 

 

2 先把要转换的字段名变成xml 元素

 

SELECT full_tab_name
,xmlelement (NAME a, colname || ',')
          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name
                      ,CASE
                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'
                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'
                          ELSE colname
                       END AS colname
                  FROM SYSCAT.COLUMNS
                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =
                          lower ('xx.xxxxx')
                ORDER BY COLNO
               ) x
          with ur;

 

如下：所有字段都套在<A></A>标签里面了，就和xml的一个元素一样

 

xmlelement(NAME a,colname ||',')  Name a 是固定格式，colname 代表from 后面的字段别名，|| ','  表示字段名后面加上个逗号

 

 

3 用xmlagg函数把分组后的字段连接

 

SELECT full_tab_name
,xmlagg (xmlelement (NAME a, colname || ','))
              ,xml2clob (xmlagg (xmlelement (NAME a, colname || ',')))
                  AS col_str
          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name
                      ,CASE
                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'
                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'
                          ELSE colname
                       END AS colname
                  FROM SYSCAT.COLUMNS
                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =
                          lower ('xxx.xxxxxxxx')
                ORDER BY COLNO
               ) x
         group by full_tab_name with ur;

 

如下图，一表已变成一条数据

 

4 替换<A></A> 标签

 

SELECT full_tab_name
              ,replace (
                  replace (
                     xml2clob (xmlagg (xmlelement (NAME a, colname || ','))),
                     '<A>',
                     ''),
                  '</A>',
                  '')
                  AS col_str
          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name
                      ,CASE
                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'
                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'
                          ELSE colname
                       END AS colname
                  FROM SYSCAT.COLUMNS
                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =
                          lower 'xxx.xxxxxx')
                ORDER BY COLNO
               ) x
         group by full_tab_name with ur;

 

结果如下：

 

 

5 获得语句

 

SELECT 'select ' || substr (col_str, 1, length (col_str) - 1) || ' from ' || full_tab_name
  FROM (SELECT full_tab_name
              ,replace (
                  replace (
                     xml2clob (xmlagg (xmlelement (NAME a, colname || ','))),
                     '<A>',
                     ''),
                  '</A>',
                  '')
                  AS col_str
          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name
                      ,CASE
                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'
                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'
                          ELSE colname
                       END AS colname
                  FROM SYSCAT.COLUMNS
                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =
                          lower ('xxx.xxxxx')
                ORDER BY COLNO
               ) x
         group by full_tab_name
       ) t WITH UR