# HackerRank: [SQL Advanced Select] (3/5) OCCUPATIONS | pivot, set, case, when, order by, group by in SQL

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

Recommended SQL Courses:

SQL 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.

Input Format:

The OCCUPATIONS table is described as follows:

 OCCUPATIONS Columns

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

Sample Input:
An OCCUPATIONS table that contains the following records:
 OCCUPATIONS Sample Data
Sample Output:
`Jenny Ashley Meera Jane Samantha Christeen Priya Julia NULL Ketty NULL Maria`

Explanation:
The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

### Solution: Using SET, CASE WHEN & ROW_NUM (MySQL Query):

`SET @d = 0, @p = 0, @s = 0, @a = 0;SELECT MIN (DOCTOR_NAMES), MIN (PROFESSOR_NAMES), MIN (SINGER_NAMES), MIN (ACTOR_NAMES)FROM  (    SELECT      CASE WHEN OCCUPATION = 'Doctor' THEN NAME END AS DOCTOR_NAMES,      CASE WHEN OCCUPATION = 'Professor' THEN NAME END AS PROFESSOR_NAMES,      CASE WHEN OCCUPATION = 'Singer' THEN NAME END AS SINGER_NAMES,      CASE WHEN OCCUPATION = 'Actor' THEN NAME END AS ACTOR_NAMES,      CASE        WHEN OCCUPATION = 'Doctor' THEN (@d := @d + 1)        WHEN OCCUPATION = 'Professor' THEN (@p := @p + 1)        WHEN OCCUPATION = 'Singer' THEN (@s := @s + 1)        WHEN OCCUPATION = 'Actor' THEN (@a := @a + 1)      END AS ROW_NUM    FROM OCCUPATIONS    ORDER BY NAME  ) AS TEMPGROUP BY ROW_NUM;`

NOTE:
1. To understand the above solution, Try to print the inner SELECT query first.
2. SET keyword is used to initiate variables like d, p, s & a with 0 value.
3. ORDER BY NAME is used to get all the names first and then NULL.
4. Here, MIN function in the outer SELECT query is used to select Name from the group of 1 Name & 3 NULL. you can use MAX instead of MIN here.
Eg. MIN(Jenny, NULL, NULL, NULL) will return  Jenny.

### Sample Output:

`Aamina Ashley Christeen EveJulia Belvet Jane JenniferPriya Britney Jenny KettyNULL Maria Kristeen SamanthaNULL Meera NULL NULLNULL Naomi NULL NULLNULL Priyanka NULL NULL`

--------------------------------------------------------------------------------