[HackerRank][SQL(Oracle)] Occupations

 

※  The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

 

■  [Advanced Select]  Occupations

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true 

Q.

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

A.

WITH D_NM AS (
	SELECT ROWNUM AS RN, NAME
	FROM (
		SELECT 	NAME
		FROM 	OCCUPATIONS
		WHERE 	OCCUPATION = 'Doctor'
		ORDER BY 1  -- NAME
	)
), P_NM AS (
	SELECT ROWNUM AS RN, NAME
	FROM (
		SELECT 	NAME
		FROM 	OCCUPATIONS
		WHERE 	OCCUPATION = 'Professor'
		ORDER BY 1
	)
), S_NM AS (
	SELECT ROWNUM AS RN, NAME
	FROM (
		SELECT 	NAME
		FROM 	OCCUPATIONS
		WHERE 	OCCUPATION = 'Singer'
		ORDER BY 1
	)
), A_NM AS (
	SELECT ROWNUM AS RN, NAME
	FROM (
		SELECT 	NAME
		FROM 	OCCUPATIONS
		WHERE 	OCCUPATION = 'Actor'
		ORDER BY 1
	)
)
SELECT	NVL(D_NM.NAME,'NULL') AS DOCTORS
		,NVL(P_NM.NAME,'NULL') AS PROFESSOR
		,NVL(S_NM.NAME,'NULL') AS SINGERS
		,NVL(A_NM.NAME,'NULL') AS ACTORS
FROM	D_NM FULL JOIN P_NM USING(RN)
		FULL JOIN S_NM USING(RN)
		FULL JOIN A_NM USING(RN);

WITH D_NM AS (
	SELECT 	ROW_NUMBER() OVER(ORDER BY NAME) AS RN, NAME
	FROM 	OCCUPATIONS
	WHERE 	OCCUPATION = 'Doctor'
), P_NM AS (
	SELECT 	ROW_NUMBER() OVER(ORDER BY NAME) AS RN, NAME
	FROM 	OCCUPATIONS
	WHERE 	OCCUPATION = 'Professor'
), S_NM AS (
	SELECT 	ROW_NUMBER() OVER(ORDER BY NAME) AS RN, NAME
	FROM 	OCCUPATIONS
	WHERE 	OCCUPATION = 'Singer'
), A_NM AS (
	SELECT 	ROW_NUMBER() OVER(ORDER BY NAME) AS RN, NAME
	FROM 	OCCUPATIONS
	WHERE 	OCCUPATION = 'Actor'
)
SELECT 	NVL(D_NM.NAME,'NULL') AS DOCTORS
		,NVL(P_NM.NAME,'NULL') AS PROFESSOR
		,NVL(S_NM.NAME,'NULL') AS SINGERS
		,NVL(A_NM.NAME,'NULL') AS ACTORS
FROM 	D_NM FULL JOIN P_NM USING(RN)
		FULL JOIN S_NM USING(RN)
		FULL JOIN A_NM USING(RN);