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,

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