The Occupation: hackerrank problem
Problem Statement:
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.
Table:
'OCCUPATIONS' table contain the column 'Name' and 'Occupation'
Explanation: in the above query statement i am using row_number() function with the 'partition by' clause to append one column that contains row_number for each row in the subquery. in the subquery while assigning row_number() we are partitioning based on the Occupation. this will assign number to the names based on their profession. to know more about partition by clause click here. Here pivot() is used to select and list the Names based on the profession used in the select statement doc,prof,singer and act respectively. max(n) referes to the names in the pivot table ..max function is used on the names to ignore the null values and select the names which are then ordered based on row_number. Hope this will clear your doubts. Thank you.
Note: The above query is written in the Oracle sql.
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.
Table:
'OCCUPATIONS' table contain the column 'Name' and 'Occupation'
Solution:
select doc,prof,singer,act
from ( select row_number() over(partition by Occupation order by Name) ron,Name as n,Occupation as o
from OCCUPATIONS ) pro
pivot
(max(n)
for o in ('Doctor' as doc,'Actor' as act,'Professor' as prof,'Singer' as singer))
order by ron asc;
from ( select row_number() over(partition by Occupation order by Name) ron,Name as n,Occupation as o
from OCCUPATIONS ) pro
pivot
(max(n)
for o in ('Doctor' as doc,'Actor' as act,'Professor' as prof,'Singer' as singer))
order by ron asc;
Explanation: in the above query statement i am using row_number() function with the 'partition by' clause to append one column that contains row_number for each row in the subquery. in the subquery while assigning row_number() we are partitioning based on the Occupation. this will assign number to the names based on their profession. to know more about partition by clause click here. Here pivot() is used to select and list the Names based on the profession used in the select statement doc,prof,singer and act respectively. max(n) referes to the names in the pivot table ..max function is used on the names to ignore the null values and select the names which are then ordered based on row_number. Hope this will clear your doubts. Thank you.
Note: The above query is written in the Oracle sql.
Comments