您可以使用row_number为课程中的每个科目指定一个编号,然后显示科目#1,#2和#3。
select
pa_cpnt.cpnt_id,
pa_cpnt.cpnt_desc,
min(case when subj.rn = 1 then subj.subj_id end) as subj_id1,
min(case when subj.rn = 2 then subj.subj_id end) as subj_id2,
min(case when subj.rn = 3 then subj.subj_id end) as subj_id3
from pa_cpnt
left outer join
(
select
cpnt_id,
subj_id,
row_number() over (partition by cpnt_id order by subj_id) as rn
from pa_cpnt_subj
) subj on subj.cpnt_id = pa_cpnt.cpnt_id
group by pa_cpnt.cpnt_id, pa_cpnt.cpnt_desc;