We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Occupations
  5. Discussions

Occupations

  • Cerebex
     + 49 comments

    This one was difficult but your code is awesome. I am going to explain this code the best way I know how to help the next guy/gal.

    First the code orders rows by name:

    SELECT * 
FROM OCCUPATIONS
ORDER BY NAME;

Output:
Aamina Doctor 
Ashley Professor 
Belvet Professor 
Britney Professor 
Christeen Singer 
Eve Actor 
Jane Singer 
Jennifer Actor 
Jenny Singer 
Julia Doctor 
Ketty Actor 
Kristeen Singer 
Maria Professor 
Meera Professor 
Naomi Professor 
Priya Doctor 
Priyanka Professor 
Samantha Actor

    Then the code creates the rows labeled with a row #, and the columns filled in with a Name or NULL value:

    set @r1=0, @r2=0, @r3=0, @r4=0;
  select case when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
  
Output:
RowNumber: Dr_Name: Prof_Name: Singer_Name: Actor_Name:
1 Aamina NULL NULL NULL 
1 NULL Ashley NULL NULL 
2 NULL Belvet NULL NULL 
3 NULL Britney NULL NULL 
1 NULL NULL Christeen NULL 
1 NULL NULL NULL Eve 
2 NULL NULL Jane NULL 
2 NULL NULL NULL Jennifer 
3 NULL NULL Jenny NULL 
2 Julia NULL NULL NULL 
3 NULL NULL NULL Ketty 
4 NULL NULL Kristeen NULL 
4 NULL Maria NULL NULL 
5 NULL Meera NULL NULL 
6 NULL Naomi NULL NULL 
3 Priya NULL NULL NULL 
7 NULL Priyanka NULL NULL 
4 NULL NULL NULL Samantha

    Finally the code aggregates the rows together using the group by RowNumber and the min statement. As stated below 'min()/max() will return a name for specific index and specific occupation. If there is a name, it will return it, if not, return NULL'. 

    set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
  select case when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
) Temp
group by RowNumber;


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

    I hope these queries help everyone better understand this great code!