如何將一列中的5組資料轉換為5個不同的列

我當前的表看起來像這樣

如果我查看一個組織，這有效，但如果我需要查看多個組織，我需要表格看起來像這樣。我沒有足夠的聲望聊天

這是我的代碼

select 
cpnt.cpnt_id,
s.ORG_ID,
pc.stud_id,
pc.compl_dte
from 
        pa_stud_program sp,
        pa_program p,
        pa_student s,
        pa_stud_cpnt pc,
        ps_program_type pt,
        pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
    and pc.compl_dte is not null
    and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
    and s.jp_id in ('1801','1805','1810','1811')
    and s.EMP_STAT_ID = 'Active'
    AND cpnt.CPNT_TYP_ID     = p.CPNT_TYP_ID
    AND cpnt.CPNT_ID         = p.CPNT_ID
    AND cpnt.REV_DTE         = p.REV_DTE
    AND pc.STUD_ID           = sp.STUD_ID
    AND sp.stud_id           = s.STUD_ID
    AND pc.CPNT_ID           = sp.CPNT_ID
    AND pc.CPNT_TYP_ID       = sp.CPNT_TYP_ID
    AND pc.REV_DTE           = sp.REV_DTE
    AND pc.seq_num           = sp.seq_num
    AND pt.PROGRAM_TYPE_ID   = p.PROGRAM_TYPE   
    /** and s.PERSON_ID_EXTERNAL  in [UserSearch]*/ 

uj5u.com熱心網友回復：

您可以使用 case 何時旋轉表格，并在外層分組。SQL代碼如下：

select ORG_ID as Organization
,sum(case when cpnt_id = 'Trainee' then 1 else 0 end) as Trainee
,sum(case when cpnt_id = 'SvcTech' then 1 else 0 end) as SvcTech
,sum(case when cpnt_id = 'CrewChief' then 1 else 0 end) as CrewChief
,sum(case when cpnt_id = 'SvcCoord' then 1 else 0 end) as SvcCoord
,sum(case when cpnt_id = 'Appr' then 1 else 0 end) as Appr
        
from (
select 
cpnt.cpnt_id,
s.ORG_ID,
pc.stud_id,
pc.compl_dte
from 
        pa_stud_program sp,
        pa_program p,
        pa_student s,
        pa_stud_cpnt pc,
        ps_program_type pt,
        pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
    and pc.compl_dte is not null
    and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
    and s.jp_id in ('1801','1805','1810','1811')
    and s.EMP_STAT_ID = 'Active'
    AND cpnt.CPNT_TYP_ID     = p.CPNT_TYP_ID
    AND cpnt.CPNT_ID         = p.CPNT_ID
    AND cpnt.REV_DTE         = p.REV_DTE
    AND pc.STUD_ID           = sp.STUD_ID
    AND sp.stud_id           = s.STUD_ID
    AND pc.CPNT_ID           = sp.CPNT_ID
    AND pc.CPNT_TYP_ID       = sp.CPNT_TYP_ID
    AND pc.REV_DTE           = sp.REV_DTE
    AND pc.seq_num           = sp.seq_num
    AND pt.PROGRAM_TYPE_ID   = p.PROGRAM_TYPE   
) as a

group by ORG_ID

uj5u.com熱心網友回復：

您可以嘗試使用樞軸來實作這一點，例如

with cpnt_org_data as (
    select Org_Id, CPNT_ID from mytable
)
select * from cpnt_org_data
pivot (
    count(1)
    for CPNT_ID in ('Trainee' as Trainee,'SvcTech' as SvcTech,'CrewChief' as CrewChief,'SvcCoord' as SvcCoord,'Appr' as Appr)
)

與您的查詢，這可能看起來像

with my_data as (
select 
cpnt.cpnt_id,
s.ORG_ID,
pc.stud_id,
pc.compl_dte
from 
        pa_stud_program sp,
        pa_program p,
        pa_student s,
        pa_stud_cpnt pc,
        ps_program_type pt,
        pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
    and pc.compl_dte is not null
    and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
    and s.jp_id in ('1801','1805','1810','1811')
    and s.EMP_STAT_ID = 'Active'
    AND cpnt.CPNT_TYP_ID     = p.CPNT_TYP_ID
    AND cpnt.CPNT_ID         = p.CPNT_ID
    AND cpnt.REV_DTE         = p.REV_DTE
    AND pc.STUD_ID           = sp.STUD_ID
    AND sp.stud_id           = s.STUD_ID
    AND pc.CPNT_ID           = sp.CPNT_ID
    AND pc.CPNT_TYP_ID       = sp.CPNT_TYP_ID
    AND pc.REV_DTE           = sp.REV_DTE
    AND pc.seq_num           = sp.seq_num
    AND pt.PROGRAM_TYPE_ID   = p.PROGRAM_TYPE 
),
cpnt_org_data as (
    select Org_Id, CPNT_ID from my_data
)
select * from cpnt_org_data
pivot (
    count(1)
    for CPNT_ID in ('Trainee' as Trainee,'SvcTech' as SvcTech,'CrewChief' as CrewChief,'SvcCoord' as SvcCoord,'Appr' as Appr)
)

在線查看作業演示資料庫小提琴

標籤：sql 甲骨文 甲骨文10g

上一篇：無法部署不存在的路徑

下一篇：將所有正則運算式匹配項作為新行回傳