妳看壹下，修改壹下就可以了
Oracle分組查詢用逗號分隔結果SQL語句
表壹：
學號 姓名
1 張三
2 李四
3 王五
。。。。
表二：
學號 選修課程
1 語文
1 數學
2 英語
2 語文
3 數學
3 英語
3 歷史
。。。。。
要求查處結果
學好 姓名 選修課程所有課程名稱以，隔開
1 張三 語文，數學
2 李四 英語，語文
3 王五 數學，英語，歷史
create table a_lyh_test
as
select 1 as "學號" , '張三' as "姓名" from dual
union all
select 2 as "學號" , '李四' as "姓名" from dual
union all
select 3 as "學號" , '王五' as "姓名" from dual
create table b_lyh_test
as
select 1 as "學號" , '語文' as "選修課程" from dual
union all
select 1 as "學號" , '數學' as "選修課程" from dual
union all
select 2 as "學號" , '英語' as "選修課程" from dual
union all
select 2 as "學號" , '語文' as "選修課程" from dual
union all
select 3 as "學號" , '數學' as "選修課程" from dual
union all
select 3 as "學號" , '英語' as "選修課程" from dual
union all
select 3 as "學號" , '歷史' as "選修課程" from dual
select f."學號"
,f."姓名"
,ltrim(max(sys_connect_by_path(f."選修課程",','))
keep (dense_rank last order by f.pnum),',') as "選修課程"
from
(
select t."學號"
,t."姓名"
,t."選修課程"
,row_number() over(partition by t."學號" order by t."姓名") as pnum
,row_number() over(partition by t."學號" order by t."姓名")-1 as lnum
from
(
select a."學號",a."姓名",b."選修課程"
from a_lyh_test a
,b_lyh_test b
where a."學號" = b."學號"
) t
) f
group by f."學號",f."姓名"
connect by f.lnum = prior f.pnum and f."學號" = prior f."學號"
start with f.pnum = 1;