db2常用方法之行转列

dongfangho

之前弄过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