Greetings!
My current code (below) shows me expected result. (I have attached an image of the formatted SQL for readability)
..........................................................................................................................................................
SELECT *
FROM (SELECT OU.SGBSTDN_PIDM PIDM,
(SELECT STVMAJR_DESC FROM STVMAJR WHERE OU.SGBSTDN_MAJR_CODE_1 = STVMAJR_CODE) MAJOR,
CASE
WHEN GOBINTL_NATN_CODE_ISSUE = 'UAE' THEN 'UAE'
ELSE 'NON_UAE'
END AS UAE_NON_UAE,
A.SPBPERS_SEX M_F
FROM SGBSTDN OU, SPBPERS A, GOBINTL
WHERE OU.SGBSTDN_COLL_CODE_1 = '04'
AND OU.SGBSTDN_STST_CODE IN ('AS','RA','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 <= '201820'
)
AND OU.SGBSTDN_PIDM = SPBPERS_PIDM
AND OU.SGBSTDN_PIDM = GOBINTL_PIDM(+)
)
PIVOT ( COUNT (PIDM)
FOR UAE_NON_UAE IN ('UAE' AS UAE, 'NON_UAE' AS NON_UAE)
)
ORDER BY MAJOR;
..........................................................................................................................................................
I get the expected result (below).
MAJOR | M_F | UAE | NON_UAE |
---|---|---|---|
Architectural Engineering | F | 97 | 286 |
Architectural Engineering | M | 5 | 89 |
Civil Engineering | F | 55 | 119 |
Civil Engineering | M | 33 | 525 |
Computer Engineering | F | 58 | 56 |
Computer Engineering | M | 63 | 77 |
Electrical & Comp. Engineering | F | 1 | 1 |
Electrical & Comp. Engineering | M | 0 | 3 |
Electrical/Electronic Engr. | F | 183 | 64 |
Electrical/Electronic Engr. | M | 125 | 180 |
Engineering Management | F | 47 | 21 |
Engineering Management | M | 38 | 47 |
Industrial Engr & Engr Mngt | F | 113 | 137 |
Industrial Engr & Engr Mngt | M | 22 | 210 |
Mechanical Engineering | F | 51 | 16 |
Mechanical Engineering | M | 74 | 242 |
Nuclear Engineering | F | 43 | 33 |
Nuclear Engineering | M | 18 | 25 |
Sustainable/Renewable Enrg Eng | F | 289 | 133 |
Sustainable/Renewable Enrg Eng | M | 78 | 129 |
What I would now like to do is bring the M_F column under UAE & Non_UAE making it look like this:
I do have a work around to this issue. I combine columns making it UAE_M, UAE_F, Non_UAE_M, & Non_UAE_F which works but it is not very elegant.
Any assistance provided would be appreciated.