wmsys.wm_concat sys_connect_by_path over

create table TABLE1
(
ID INTEGER,
NAME VARCHAR2(10)
)
create table TABLE2
(
ID INTEGER,
ROLE VARCHAR2(10)
)
insert into TABLE1 (ID, NAME) values (1, '张三');
insert into TABLE1 (ID, NAME) values (2, '李四');
commit;
insert into TABLE2 (ID, ROLE) values (1, '查询');
insert into TABLE2 (ID, ROLE) values (1, '分析');
insert into TABLE2 (ID, ROLE) values (1, '决策');
insert into TABLE2 (ID, ROLE) values (2, '查询');
commit;


输出结果：

Sql代码

ID NAME ROLE
1 张三 查询,分析,决策
2 李四 查询



方法一:使用wmsys.wm_concat

Sql代码
select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id
group by table1.id,table1.name

方法二:使用sys_connect_by_path

Sql代码
select id, name, ltrim(max(sys_connect_by_path(role, ',')), ',') from
(select row_number() over(partition by table1.id order by name) rn,table1.*, role from table1, table2 where table1.id =
table2.id)
start with rn = 1
connect by prior rn = rn - 1 and prior id = id
group by id, name
order by id