Transpose rows into columns

TABLE EMP_B
EMP_ID/EMP_NAME

111/TEST RARO/
112/ICHIGO MUJIKO
113/NORISHIMA ICHIRO
…

Table EMP_PHONE
(One employee may have up to 3 types of phone numbers, Type 1 – home, type2 – office and type 3 - cell)

EMP_ID/TYPE/PHONE NUMBER

111/1/037378877987
111/2/048880000078
112/1/034878887888
112/2/044545645878
112/3/090945564456
114/1/456777899889

With the following query, you can get all the phone number into one row for one employee


SELECT a.emp_id,
a. emp_name,
NVL(b.phone1, ‘NA’) phone_1,
NVL(b.phone2, ‘NA’) phone_2,
NVL(b.phone3, ‘NA’) phone_3
FROM
EMP_B a,
(SELECT EMP_ID
MIN(DECODE(TYPE, 1, PHONE_NUMBER, NULL)) PHONE1,
MIN(DECODE(TYPE, 2, PHONE_NUMBER, NULL)) PHONE2,
MIN(DECODE(TYPE, 3, PHONE_NUMBER, NULL)) PHONE3
FROM EMP_PHONE
GROUP BY EMP_ID) b
WHERE a.emp_id = b.emp_id (+)

The result will be

EMP_ID EMP_NAME PHONE_1 PHONE_2 PHONE_3

111 TEST_RARO 037378877987 048880000078 NA
112 ICHIGO_MUJIKO 034878887888 044545645878 90945564456
113 NORISHIMA ICHIRO NA NA NA