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.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:

1. Udemy: SQL - MySQL for Data Analytics and Business Intelligence
2. LinkedIn: Master SQL for Data Science
3. edX: Databases: Advanced Topics in SQL
4. edureka: SQL Essentials Training & Certification
5. Coursera: Learn SQL Basics for Data Science
6. Eduonix: SQL Queries

---

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 TEMP
GROUP 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 Eve
Julia Belvet Jane Jennifer
Priya Britney Jenny Ketty
NULL Maria Kristeen Samantha
NULL Meera NULL NULL
NULL Naomi NULL NULL
NULL Priyanka NULL NULL

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

Click here to see solutions for all Machine Learning Coursera Assignments.

&

Click here to see more codes for Raspberry Pi 3 and similar Family.

&

Click here to see more codes for NodeMCU ESP8266 and similar Family.

&

Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.

Feel free to ask doubts in the comment section. I will try my best to answer it.

If you find this helpful by any mean like, comment and share the post.

This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,

-Akshay P Daga