表t id col1 col2 1 a 88 SELECT id, wm_concat(col1||' '||col2) FROM t group by id;
with tab as ( select '2' as ids, 'a' as nm,'88' as va from dual union select '2' , 'b', '99' from dual union select '2' ,'c','100' from dual union select '3', 'd','66' from dual union select '3','e' ,'44' from dual ) select ids ,ltrim(max(sys_connect_by_path(nm||' '||va||' ',',')),',') from ( select nm,va, ids ,row_number() over(partition by ids order by ids) d from tab ) start with d=1 connect by prior d =d-1 AND ids = PRIOR ids group by ids
SQL> with t as( 2 select 2 ids,'a' nm,88 va from dual union all 3 select 2,'b',99 from dual union all 4 select 2,'c',100 from dual union all 5 select 3,'d',66 from dual union all 6 select 3,'e',44 from dual union all 7 select 3,'f',33 from dual union all 8 select 3,'g',18 from dual union all 9 select 4,'h',25 from dual union all 10 select 4,'j',26 from dual) 11 select ids,replace(wm_concat(nm||' '||va),',',' ') col_2 12 from t 13 group by ids 14 /
IDS COL_2 ---------- -------------------------------------------------------------------------------- 2 a 88 b 99 c 100 3 d 66 g 18 e 44 f 33 4 h 25 j 26
id col1 col2
1 a 88 SELECT id, wm_concat(col1||' '||col2) FROM t group by id;
(
select '2' as ids, 'a' as nm,'88' as va from dual
union
select '2' , 'b', '99' from dual
union
select '2' ,'c','100' from dual
union
select '3', 'd','66' from dual
union
select '3','e' ,'44' from dual
)
select ids ,ltrim(max(sys_connect_by_path(nm||' '||va||' ',',')),',') from
(
select nm,va, ids ,row_number() over(partition by ids order by ids) d from tab
)
start with d=1
connect by prior d =d-1
AND ids = PRIOR ids
group by ids
SQL> with t as(
2 select 2 ids,'a' nm,88 va from dual union all
3 select 2,'b',99 from dual union all
4 select 2,'c',100 from dual union all
5 select 3,'d',66 from dual union all
6 select 3,'e',44 from dual union all
7 select 3,'f',33 from dual union all
8 select 3,'g',18 from dual union all
9 select 4,'h',25 from dual union all
10 select 4,'j',26 from dual)
11 select ids,replace(wm_concat(nm||' '||va),',',' ') col_2
12 from t
13 group by ids
14 /
IDS COL_2
---------- --------------------------------------------------------------------------------
2 a 88 b 99 c 100
3 d 66 g 18 e 44 f 33
4 h 25 j 26