db2常用方法之行转列
之前弄过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 dual
union all
select 'b' A, 1 B, 'www' C
from dual
union all
select 'c' A, 3 B, 'sss' C
from dual
union all
select 'd' A, 4 B, 'ddd' C from dual)
/*
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
页:
[1]