如何在oracle中没有数据的列中显示空格

小编典典

如何在oracle中没有数据的列中显示空格

sql

我有两个表，在第一个表中存储课程ID，在第二个表中存储课程ID和不同的学科领域描述，如下所示。

Table PA_CPNT
CPNT_ID( Course ID)    Course Title 
06201826               AAAA
06201827               BBBB
06201828               CCCC

Table PA_CPNT_SUBJ
CPNT_ID     SUBJ_ID 
06201826    PLNT_DEV
06201826    WRKS_COUN 
06201827    WRKS_COUN1
06201827    WRKS_COUN2
06201827    WRKS_COUN3
06201828    WRKS_COUN

My requirement is to have an output in the below format


CPNT_ID   COUrse Title   SUBJ_ID1    SUBJ_ID2     SUBJ_ID3
06201826  AAAA           PLNT_DEV    WRKS_COUN 
06201827  BBBB           WRKS_COUN1  WRKS_COUN2   WRKS_COUN3 
06201828  CCCC           WRKS_COUN

我已经编写了以下代码，如何修改此代码以实现上述要求。

select distinct CPNT_ID,
cpnt_desc,
SUBJ_ID1,
SUBJ_ID2,
SUBJ_ID3
from
(
 select a.cpnt_id, 
 a.cpnt_desc,
 b.subj_id as subj_id1, 
 c.subj_id as subj_id2,
 d.subj_id as subj_id3
 from PA_CPNT a 
 inner join PA_CPNT_SUBJ b
  on a.cpnt_id=b.cpnt_id
  inner join PA_CPNT_SUBJ c
 on a.cpnt_id=c.cpnt_id
 inner join PA_CPNT_SUBJ d
 on a.cpnt_id=d.cpnt_id
   ) X
where subj_id1 ! = subj_id2
and subj_id2 ! = subj_id3
and subj_id3 ! = subj_id1

请帮忙

阅读 216

2021-05-16

共1个答案

小编典典

您可以使用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;

2021-05-16