Toad World® Forums

Two rows of headers


#1

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:

Formatted

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.