如何在oracle中没有数据的列中显示空格 [英] How to show spaces in columns which do not have data in oracle

问题描述

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

I have two tables, in the first table the course id is stored and in the second table the course id and different subject areas description are stored as shown below.

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

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

I have written the below code, how can I modify this code to achieve the above requirement.

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

请帮助

推荐答案

您可以使用row_number为课程中的每个科目指定一个编号，然后显示科目#1，#2和#3.

You can use row_number to give each subject in a course a number, then show subject #1, #2 and #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;

这篇关于如何在oracle中没有数据的列中显示空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！