17,086
社区成员
发帖
与我相关
我的任务
分享
create table t(X int, Y int, Z varchar2(10));
insert into t values(1, 11, 'a');
insert into t values(1, 12, 'b');
insert into t values(1, 13, 'c');
insert into t values(1, 1211, 'g');
insert into t values(3, 31, 'e');
insert into t values(3, 32, 'f');
with t1 as (
select x, y, z, ','||wm_concat(y) over (partition by x)||',' y1 from t
)
select x,
regexp_replace(regexp_replace(replace(y1, ','||y||',', ','), '^,',''), ',$', '') y,
z from t1;
---结果--
X Y Z
---------- -------------------- ----------
1 12,1211,13 a
1 11,1211,13 b
1 11,12,13 g
1 11,12,1211 c
3 31 f
3 32 e
SELECT X,
RTRIM(LTRIM(REPLACE(REPLACE(WM_CONCAT() OVER(PARTITION BY X), Y, ''),
',,',
','),
','),
',') Y,
Z
FROM (SELECT * FROM T ORDER BY X, Z)