WITH temp AS (SELECT 'A' id,'XX' name FROM dual UNION ALL SELECT 'A' id,'XY' name from dual UNION ALL SELECT 'A' id,'YY' name from dual UNION ALL SELECT 'B' id,'aa' name from dual UNION ALL SELECT 'B' id,'bb' name from dual) SELECT id,REPLACE(wm_concat(name),',',';') FROM temp GROUP BY id;
select grade,replace(wm_concat(name),',',';') from temp group by grade
SQL> with t as( 2 select 'A' grade,'XX'name from dual union all 3 select 'A','XY' from dual union all 4 select 'A','YY' from dual union all 5 select 'B','aa' from dual union all 6 select 'B','bb' from dual 7 ) 8 select grade,wmsys.wm_concat(name) name 9 from t 10 group by grade;GRADE NAME ----- -------------------------------------------------------------------------------- A XX,XY,YY B aa,bb
--考虑分号之间连接的name如果有顺序,这样: --例按name升序组合:select grade,max(replace(name,',',';')) name from ( select grade,wm_concat(name)over(partition by grade order by name) name from tab ) group by grade
with t as( select 'A' grade,'XX'name from dual union all select 'A','XY' from dual union all select 'A','YY' from dual union all select 'B','aa' from dual union all select 'B','bb' from dual ) select grade,replace(wm_concat(name),',',';') name from t group by grade; GRADE NAME ----- -------------------------------------------------------------------------------- A XX;XY;YY B aa;bb
SQL> with tb as( 2 select 'A' grade,'XX'name from dual union all 3 select 'A','XY' from dual union all 4 select 'A','YY' from dual union all 5 select 'B','aa' from dual union all 6 select 'B','bb' from dual 7 ) 8 select grade,substr(max(sys_connect_by_path(name,';')),2) name 9 from (select grade,name,row_number() over(partition by grade order by name) rn from tb) 10 start with rn=1 11 connect by rn= prior rn+1 and connect_by_root(grade)=grade 12 group by grade 13 /
GRADE NAME ----- -------------------------------------------------------------------------------- A XX;XY;YY B aa;bb
SQL> ed SQL> SQL> with tb as( 2 select 'A' grade,'XX'name from dual union all 3 select 'A','XY' from dual union all 4 select 'A','YY' from dual union all 5 select 'B','aa' from dual union all 6 select 'B','bb' from dual 7 ) 8 select grade,wm_concat(name) from tb group by grade 9 /
GRADE WM_CONCAT(NAME) ----- -------------------------------------------------------------------------------- A XX,XY,YY B aa,bb
(SELECT 'A' id,'XX' name FROM dual
UNION ALL
SELECT 'A' id,'XY' name from dual
UNION ALL
SELECT 'A' id,'YY' name from dual
UNION ALL
SELECT 'B' id,'aa' name from dual
UNION ALL
SELECT 'B' id,'bb' name from dual)
SELECT id,REPLACE(wm_concat(name),',',';') FROM temp
GROUP BY id;
SQL> with t as(
2 select 'A' grade,'XX'name from dual union all
3 select 'A','XY' from dual union all
4 select 'A','YY' from dual union all
5 select 'B','aa' from dual union all
6 select 'B','bb' from dual
7 )
8 select grade,wmsys.wm_concat(name) name
9 from t
10 group by grade;GRADE NAME
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb
--考虑分号之间连接的name如果有顺序,这样:
--例按name升序组合:select grade,max(replace(name,',',';')) name from (
select grade,wm_concat(name)over(partition by grade order by name) name
from tab
)
group by grade
with t as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual
)
select grade,replace(wm_concat(name),',',';') name
from t
group by grade;
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
SQL> with tb as(
2 select 'A' grade,'XX'name from dual union all
3 select 'A','XY' from dual union all
4 select 'A','YY' from dual union all
5 select 'B','aa' from dual union all
6 select 'B','bb' from dual
7 )
8 select grade,substr(max(sys_connect_by_path(name,';')),2) name
9 from (select grade,name,row_number() over(partition by grade order by name) rn from tb)
10 start with rn=1
11 connect by rn= prior rn+1 and connect_by_root(grade)=grade
12 group by grade
13 /
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
SQL> ed
SQL>
SQL> with tb as(
2 select 'A' grade,'XX'name from dual union all
3 select 'A','XY' from dual union all
4 select 'A','YY' from dual union all
5 select 'B','aa' from dual union all
6 select 'B','bb' from dual
7 )
8 select grade,wm_concat(name) from tb group by grade
9 /
GRADE WM_CONCAT(NAME)
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb