> For the complete documentation index, see [llms.txt](https://dshub.gitbook.io/ds-hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/hackerrank/sql-intermediate/occupations.md).

# Occupations

[Question Link](https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=false)

[Pivot](https://en.wikipedia.org/wiki/Pivot_table) 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:

![](https://s3.amazonaws.com/hr-challenge-images/12889/1443816414-2a465532e7-1.png)

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

**Sample Input**

![](https://s3.amazonaws.com/hr-challenge-images/12890/1443817648-1b2b8add45-2.png)

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

```sql
/*
The MAX (or MIN) is needed so that MySQL fills in the unique column value when 
scanning across the 4 values per row in the cte (Common Table Expression), 
not necessarily in the column order required.
Removal of the aggregation MAX (or MIN) leads to a syntax error, which I believe 
is due to violation of 2NF (2nd normal form), as there must be some dependence
of these attributes on the key n.
*/


WITH cte AS (
    SELECT 
        Name, 
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS n
    FROM Occupations
)

SELECT 
    MAX(IF(occupation="Doctor",name,NULL)) as Doctor,
    MAX(IF(occupation="Professor",name,NULL)) as Professor,
    MAX(IF(occupation="Singer",name,NULL)) as Singer,
    MAX(IF(occupation="Actor",name,NULL)) as Actor
FROM cte 
GROUP BY n;
```