Question

db2中如何实现 wmsys.wm_concat功能

Answer 1

法一：
SELECT REPLACE(replace(xml2clob(xmlagg(xmlelement(name A, 'A.'||name||', '))),'</A>',''),'<A>','') AS COLNAME
from sysibm.syscolumns

法二：递归SQL法
WITH T1(A,NUM) AS
(
Select name,ROW_NUMBER() OVER(PARTITION BY tbname ORDER BY tbname) as num
FROM sysibm.syscolumns
where tbname='T_SZPX_YL_GPXXB_TEST'
),
t2(ra,num) as(
Select A as a,NUM FROM T1 Where NUM = 1
UNION all
Select RTRIM(T2.RA)||T1.A,t1.num FROM T1 , T2 Where T1.NUM = T2.NUM + 1)
select ra,num from t2
where t2.num=(select max(num) from t1)

Answer 2

使用递归，看看这个例子：
with
t (t1,t2,t3) as (
values
('A1', 1 ,'01'),
('A1' ,1, '02'),
('A1', 1, '03'),
('A2', 1, '01'),
('A2', 2, '01'),
('A2', 2 ,'02')
),
t1(t11,t22,t33,t44,t55) as (
select t1,t2,t3,
rownumber() over(partition by t1,t2),
rownumber() over(partition by t1,t2) as t4
from t
),
t3(s1,s2,s3,s4,s5) as(
select t11,t22,cast(t33 as varchar(100)),t44,t55 from t1 where T44 =1 and t55=1
union all
select a.s1,a.s2,cast(a.s3||','||b.t33 as varchar(100)),a.s4+1,a.s5 from t3 a,t1 b
where a.s1=b.t11 and a.s4 = b.t55-1)
select s1,s2,s3
from t3 x
where x.s4=(select max(s4) from t3 y where x.s1=y.s1);

追问

TABLE1 TABLE3 TABLE2
A1 B1 A3 B3 A2 B2
(1) a (1) 一 一 D
(3) g (3) 二 二 P
(1) h (5) 四 一 M
(5) d 一 a
(5) s 四 K
(5) c 四 K
结果：
一 a,h,D,M,
二 g,p
四 d,s,c,K
oracle中我已用表连接和“wmsys.wm_concat”实现，但是现在想用DB2实现。
谢谢你的回答，但是还是没有看懂。