db2中如何实现 wmsys.wm_concat功能说的详细一点越详细越好,跪谢,贵...

发布网友发布时间：2022-05-05 03:22

共1个回答

热心网友时间：2022-04-13 19:35

摘自博客：
之前弄过oracle的行转列了，现在研究下db2的行转列.
db2函数没有oracle那么强大，像oracle，几行代码能解决的问题换成db2的话，需要更多的代码了

下面粘出来，大家看下。要是发现问题给我留言哈

1.多行转一行
WITH rs as ( select name,row_number() over() RN from T_test),
RPL (RN, name) AS
(
select ROOT.RN,CAST(ROOT.name AS VARCHAR(2000)) from rs ROOT
UNION ALL
SELECT CHILD.RN, CHILD.name||','||PARENT.nameFROM RPL PARENT, rs CHILD WHERE PARENT.RN +1 = CHILD.RN
)
select MAX(name) namefrom RPL GROUP BY RN ORDER BY RN DESC
fetch first 1 rows only
2。行转列和oracle相同
===================================
with rs as(
select 'a' A, 2 B, 'www' C
from al
union all
select 'b' A, 1 B, 'www' C
from al
union all
select 'c' A, 3 B, 'sss' C
from al
union all
select 'd' A, 4 B, 'ddd' C from al)
/*
select rs.C,
case rs.A　when　 'a' then　rs.B　else　0 end　ver_high,
case rs.A　when　 'b' then　rs.B　else　0 end　v_high,
case rs.A　when　 'c' then　rs.B　else　0 end　v_low,
case rs.A　when　 'd' then　rs.B　else　0 end　ver_low
from rs
*/
select rs.C,
sum(case rs.A　when　 'a' then　rs.B　else　0 end)　ver_high,
sum(case rs.A　when　 'b' then　rs.B　else　0 end)　v_high,
sum(case rs.A　when　 'c' then　rs.B　else　0 end)　v_low,
sum(case rs.A　when　 'd' then　rs.B　else　0 end)　ver_low
from rs
group by C from rs