SQL Scenario 1

Scripts to test

CREATE TABLE PEOPLE_DETAILS
(
NAME VARCHAR(100),
OCCUPATION VARCHAR(100)
);


INSERT INTO PEOPLE_DETAILS VALUES ('SAMANTHA' , 'DOCTOR');
INSERT INTO PEOPLE_DETAILS VALUES ('JULIA' , 'ACTOR');
INSERT INTO PEOPLE_DETAILS VALUES ('MARIA' , 'ACTOR');
INSERT INTO PEOPLE_DETAILS VALUES ('MEERA' , 'SINGER');
INSERT INTO PEOPLE_DETAILS VALUES ('ASHLEY' , 'PROFESSOR');
INSERT INTO PEOPLE_DETAILS VALUES ('KETTY' , 'PROFESSOR');
INSERT INTO PEOPLE_DETAILS VALUES ('CHRISTEEN' , 'PROFESSOR');
INSERT INTO PEOPLE_DETAILS VALUES ('JANE' , 'ACTOR');
INSERT INTO PEOPLE_DETAILS VALUES ('JENNY' , 'DOCTOR');
INSERT INTO PEOPLE_DETAILS VALUES ('PRIYA' , 'SINGER');

COMMIT;

SELECT * FROM PEOPLE_DETAILS;


SELECT
CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME END AS NAME1,
CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME END AS NAME2,
CASE WHEN OCCUPATION = 'SINGER' THEN NAME END AS NAME3,
CASE WHEN OCCUPATION = 'ACTOR' THEN NAME END AS NAME4,
RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME)RN
FROM PEOPLE_DETAILS;



SELECT MAX(NAME1),MAX(NAME2),MAX(NAME3),MAX(NAME4) FROM (
SELECT CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME END AS NAME1,
CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME END AS NAME2,
CASE WHEN OCCUPATION = 'SINGER' THEN NAME END AS NAME3,
CASE WHEN OCCUPATION = 'ACTOR' THEN NAME END AS NAME4,
RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME)RN
FROM PEOPLE_DETAILS) GROUP BY RN ORDER BY RN;