Pivoting an SQL script that already has pivot

Toad for Oracle
1

Greetings!

I currently have a script with pivot function in it. My script groups result based on nationalities (UAE & Non-UAE). I would like to further group it by gender. The ‘Desired’ output shows how I would like to display my result. Will I be able to further pivot my result to break down the nationality group by gender? Below you will find my script, result, and desired output.

Script

…

select *

from

(
select Ou.SGBSTDN_PIDM PIDM, decode(substr(Ou.SGBSTDN_CAMP_CODE,3,1),‘M’,‘M’,‘W’,‘W’) Gender, STVCOLL_DESC College,
STVMAJR_DESC Major,
case
when STVNATN_NATION = ‘United Arab Emirates’ then ‘UAE’
else ‘Non_UAE’
end as UAE_Non_UAE

from SPRIDEN, SGBSTDN Ou, STVCOLL, STVMAJR, GOBINTL, STVNATN

where SPRIDEN_PIDM = Ou.SGBSTDN_PIDM
and SPRIDEN_CHANGE_IND is null
and Ou.SGBSTDN_STST_CODE in (‘AS’,‘AW’,‘CW’,‘DA’,‘DW’,‘OW’,‘PA’,‘RA’,‘SP’,‘WN’,‘XP’,‘XT’)
and Ou.SGBSTDN_TERM_CODE_EFF = ( select max(Inn.SGBSTDN_TERM_CODE_EFF)
from SGBSTDN Inn
where Inn.SGBSTDN_PIDM = Ou.SGBSTDN_PIDM
and Inn.SGBSTDN_TERM_CODE_EFF <= ‘201610’
)
and (Ou.SGBSTDN_COLL_CODE_1 = ‘05’ or Ou.SGBSTDN_MAJR_CODE_1 in (‘H094’, ‘NURS’, ‘NURB’))
and Ou.SGBSTDN_COLL_CODE_1 = STVCOLL_CODE
and Ou.SGBSTDN_MAJR_CODE_1 = STVMAJR_CODE
and Ou.SGBSTDN_PIDM = GOBINTL_PIDM(+)
and GOBINTL_NATN_CODE_LEGAL = STVNATN_CODE(+)
)

pivot

(
count (PIDM)–, count (Gender) as MW
for UAE_Non_UAE in (‘UAE’ as UAE, ‘Non_UAE’ as Non_UAE)
)

order by College, Major
;

…

Result

…

GENDER
COLLEGE
MAJOR
UAE
NON_UAE
M
Health Sciences
Clinical Nutrition & Dietetics
0
2
W
Health Sciences
Clinical Nutrition & Dietetics
22
209
M
Health Sciences
Environmental Health
2
12
W
Health Sciences
Environmental Health
24
23
M
Health Sciences
Health Services Administration
1
5
W
Health Sciences
Health Services Administration
6
45
M
Health Sciences
Medical Diagnostic Imag - Bdg
0
6
W
Health Sciences
Medical Diagnostic Imag - Bdg
0
3
…

Desired

…

UAE
Non_UAE
COLLEGE
MAJOR
M
W
M
W
Health Sciences
Clinical Nutrition & Dietetics
0
22
2
209
Health Sciences
Environmental Health
2
24
12
23
Health Sciences
Health Services Administration
1
6
5
45
Health Sciences
Medical Diagnostic Imag - Bdg
0
0
6
3
…

2

Just put your entire query into a subquery, and then add a pivot to sort out the UAE/NonUAE Male/Female

So…

Select * from

(<your entire query so far, including the “select * from”, and the pivot, but without the order by and without the semicolon>)

then, for the “2nd” pivot, add

Pivot ()

3

Thanks John for your prompt reply.

I followed your suggestion but it did not work. I tried different variations of pivot options. It seems like I am doing something wrong. The modified code is as follows:

select PIDM, Gender, College, Major–, UAE_Non_UAE
from (
select *
from (
select Ou.SGBSTDN_PIDM PIDM, decode(substr(Ou.SGBSTDN_CAMP_CODE,3,1),‘M’,‘M’,‘W’,‘W’) Gender, STVCOLL_DESC College,
STVMAJR_DESC Major,
case
when STVNATN_NATION = ‘United Arab Emirates’ then ‘UAE’
else ‘Non_UAE’
end as UAE_Non_UAE
from SPRIDEN, SGBSTDN Ou, STVCOLL, STVMAJR, GOBINTL, STVNATN
where SPRIDEN_PIDM = Ou.SGBSTDN_PIDM
and SPRIDEN_CHANGE_IND is null
and Ou.SGBSTDN_STST_CODE in (‘AS’,‘AW’,‘CW’,‘DA’,‘DW’,‘OW’,‘PA’,‘RA’,‘SP’,‘WN’,‘XP’,‘XT’)
and Ou.SGBSTDN_TERM_CODE_EFF = ( select max(Inn.SGBSTDN_TERM_CODE_EFF)
from SGBSTDN Inn
where Inn.SGBSTDN_PIDM = Ou.SGBSTDN_PIDM
and Inn.SGBSTDN_TERM_CODE_EFF <= ‘201610’
)
and (Ou.SGBSTDN_COLL_CODE_1 = ‘05’ or Ou.SGBSTDN_MAJR_CODE_1 in (‘H094’, ‘NURS’, ‘NURB’))
and Ou.SGBSTDN_COLL_CODE_1 = STVCOLL_CODE
and Ou.SGBSTDN_MAJR_CODE_1 = STVMAJR_CODE
and Ou.SGBSTDN_PIDM = GOBINTL_PIDM(+)
and GOBINTL_NATN_CODE_LEGAL = STVNATN_CODE(+)
)
pivot (
count (PIDM)–, count (Gender) as MW
for UAE_Non_UAE in (‘UAE’ as UAE, ‘Non_UAE’ as Non_UAE)
)
)
pivot (
Count (Gender) --Count (PIDM),
–for UAE_Non_UAE in (‘UAE’ as UAE, ‘Non_UAE’ as Non_UAE)
for Gender in (‘M’ as M, ‘W’ as W)
)
order by College, Major
;

4

Hi Jollydoe,

“It didn’t work” is a little vague. Was there an error? If so, what was it? Or did the query run OK but not produce the result you were looking for? Please be more precise.

Not having your original tables, I decided to just make a table with the data of your original result set. So I made a table called “JOLLY” with columns GENDER, COLLEGE, MAJOR, UAE, and NON_UAE and filled it with those 8 rows.

There may be a way to get the result that you are looking for with PIVOT, but to be honest I haven’t used PIVOT very much, so I decided to tackle this with SUM and DECODE. I was able to get your desired result with this query:

select college, major,
sum(decode(gender, ‘M’, UAE, 0)) as UAE_M,
sum(decode(gender, ‘W’, UAE, 0)) as UAE_W,
sum(decode(gender, ‘M’, NON_UAE, 0)) as NON_UAE_M,
sum(decode(gender, ‘W’, NON_UAE, 0)) as NON_UAE_W
from jolly
group by college, major

and one way you could turn your query into mine is

with JOLLY AS
(<your prior query here, with pivot but without order by>)
select college, major,
sum(decode(gender, ‘M’, UAE, 0)) as UAE_M,
sum(decode(gender, ‘W’, UAE, 0)) as UAE_W,
sum(decode(gender, ‘M’, NON_UAE, 0)) as NON_UAE_M,
sum(decode(gender, ‘W’, NON_UAE, 0)) as NON_UAE_W
from JOLLY
group by college, major

5

Hi John,

Sorry about the vagueness. I will not get into error detail for two reasons – your alternate solution worked for me & since you have not used PIVOT much, I will not bother you with PIVOT error issues. If you still want to know the error, please let me know. I will gladly share it with you.

Thank you for providing an alternate solution. Going forward, I will use this solution whenever I need to perform double pivot functions.