关于 行列相互转行

最近遇到几次 行，列相互 转换的问题，做下总结：

行转列和列转行

1，列转行：

数据：ver    qname
      01     A,B,C

想通过SQL使查询结果变成： 
ver    qname
01     A
01     B
01     C

SQL:

with n (str, ori, pos) as (
select 'A,B,C'||',', 1, posstr('A,B,C'||',', ',')
from sysibm.sysdummy1
union all 
select str, pos+1, locate(',', str, pos+1)
from n 
where locate(',', str, pos+1) > 0)
select str, ori, pos, substr(str, ori, pos-ori) as result from n

2，行转列：

数据：ver    qname
      01     A
      01     B
      01     C

想通过SQL使查询结果变成： 
     ver    qname
     01     A,B,C

SQL:
   SELECT VAR, max(Sys_Connect_By_Path(z.NAME, ',')) name

     FROM (SELECT m.VAR,
               M.NAME,
               VAR + Row_Number() Over(ORDER BY VAR) Rn,
               Row_Number() Over(PARTITION BY VAR ORDER BY VAR, NAME) Rn1
          FROM (SELECT VAR, NAME
                  FROM (VALUES('01', 'A'), ('01', 'B'), ('01', 'C')) AS(VAR, NAME)) m) z
 START WITH Rn1 = 1
CONNECT BY Rn - 1 = PRIOR Rn
 GROUP BY VAR
  注:这个例子需要开启DB2的一个注册表变量 db2set DB2_COMPATIBILITY_VECTOR=08 (DB2兼容Oracle)，要重启数据库的

 

  listagg v9.7.4新增的聚集函数（行转列）

   http://www.db2china.net/home/space.php?uid=41247&do=blog&id=13044

 

3，不同类型的行转列

 例子1：
     表B:  Id, card_id, flag, name
     1    1       1     张
     2    1       0     李
     3    2       1     王
     4    2       0     刘

       Card_id相同的记录一定有2条。

       问题：将card_id相同的数据的name用VS连接起来输出，数据flag为1的name放VS前面，flag为0的放后面。

       检索出来的结果是：card_id   hb
                         1        张 VS 李
                         2        王 VS 刘

 例子2：
     表C：Id, ztid, gdmc, bfbl  
           1    11  股东a  30
           2    11  股东b  70
           3    12  股东c  40
           4    12  股东d  40 
           5    12  股东e  20 
           6    13  股东f  25
           7    13  股东g  25
           8    13  股东h  25 
           9    13  股东i  25
        结果显示成
       ztid   gdxx
        11  股东a:30%,股东b:70%
        12  股东c:40%,股东d:40%,股东e:20%
        13  股东f:25%,股东g:25%,股东h:25%,股东i:25%

 以上都是行转换成列的例子，但是2个区别很大
  例子1 同一 card_id 的记录数都是2，并且有flag列做区分
  例子2 同一 ztid 的记录数不完全一样

  对于例子1，可以很简单的使用:
    select card_id,max(decode(flag,1,name))||' VS '||max(decode(flag,0,name))
      from b
      group by card_id
    
     此外相似的例子还有
      表结构：TEST_TB_GRADE
         ID USER_NAME COURSE SCORE
         1  张三      数学    90
         2  张三      语文    85
         3  张三      英语    85
         4  李四      数学    89
         5  李四      语文    90
         6  李四      英语    91
         7  王五      数学    100 
         8  王五      语文    95 
         9  王五      英语    97
     结果显示为：
      user_name math chinese  english 
       张三     90      85       85
       李四     89      90       91 
       王五     100     95       91
    关于此类 行转列的扩展： http://www.2cto.com/database/201108/100792.html
  
  对于例子2，就要使用上面“2，行转列：”提到的方法