db2中如何实现 wmsys.wm_concat功能

发布网友发布时间：2022-05-24 12:50

共2个回答

热心网友时间：2023-10-14 04:38

法一：
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)

热心网友时间：2023-10-14 04:38

使用递归，看看这个例子：
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);追问TABLE1TABLE3TABLE2
A1B1A3B3A2B2
(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实现。
谢谢你的回答，但是还是没有看懂。