最近遇到几次 行,列相互 转换的问题,做下总结:
行转列和列转行
1,列转行:
数据:ver qname
01 A,B,C
01 A,B,C
想通过SQL使查询结果变成:
ver qname
01 A
01 B
01 C
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
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
01 A
01 B
01 C
想通过SQL使查询结果变成:
ver qname
01 A,B,C
ver qname
01 A,B,C
SQL:
SELECT VAR, max(Sys_Connect_By_Path(z.NAME, ',')) name
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),要重启数据库的
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新增的聚集函数(行转列)
3,不同类型的行转列
例子1:
表B: Id, card_id, flag, name
1 1 1 张
2 1 0 李
3 2 1 王
4 2 0 刘
表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 刘
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%
表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 同一 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,行转列:”提到的方法