How to turn 5 sets of data from one column into 5 different columns

Question

My current table looks like this

CPNT_ID	Org_Id	Stud ID	Compl_Dte
Trainee	Org001	101010	Nov 13, 2016
SvcTech	Org001	101010	Nov 13, 2016
CrewChief	Org001	101010	Nov 13, 2016
Trainee	Org001	101013	Nov 13, 2016
SvcTech	Org001	101013	Nov 13, 2016
Trainee	Org002	101011	Nov 13, 2016
SvcTech	Org002	101011	Nov 13, 2016
Trainee	Org002	101012	Nov 13, 2016

This works if im looking at one organization, but if i need to see multiple organizations I need the table to look like this. I don't have enough reputation to chat

Organization	Trainee	SvcTech	CrewChief	SvcCoord	Appr
Org001	2	2	1	0	0
Org002	2	1	0	0	0

This is my code

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]*/

Littlefoot if you see this I couldnt get org to fit into the other query so I rewrote it in hopes you can make it fit into this one. — Mr Grimm, Oct 28 '21 at 00:03
Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Oct 28 '21 at 05:44

score 1 · Accepted Answer · answered Oct 28 '21 at 04:01

You can use case when to pivot the table,and group by in outer layer. SQL code is following:

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

Thank you. This worked. Any chance you know how I can get this to be a unique count. I had the solution working vertically from this thread but now I need it horizontally... that's what she said. https://stackoverflow.com/questions/69734574/how-to-subtract-two-specific-rows-and-create-custom-column-with-answer — Mr Grimm, Oct 28 '21 at 15:26
I am afraid you need ask another question because I am kind of confused by these question. — didadidaaaaa, Oct 29 '21 at 02:31

How to turn 5 sets of data from one column into 5 different columns

1 Answers1