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. Practice
  2. SQL
  3. Advanced Select
  4. Occupations
  5. Discussions

Occupations

Sort 876 Discussions, By:

  • YujiShen
     + 50 comments

    My answer in MySQL. I have to keep track of row number of each Occupation for pivoting. Using either max()/min() can get first non-null value after group by.

    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

  • raiyanger24
     + 17 comments

    Let me break it down in steps (answer in MySQL)

    Step 1:

    Create a virtual table in your head of the data given to us. It look like this https://imgur.com/u6DEcNQ

    SELECT
    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

    Step 2:

    Create an index column with respect to occupation as "RowNumber".https://imgur.com/QzVCWFn

    Notice from the image, under professor column, the first Name is indexed as 1, the next name "Birtney" as 2. That is what I mean by index w.r.t occupation.

    The below code will only give the "RowNumber" column, to get the result like in image proceed to step 3.

    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

FROM OCCUPATIONS

    Step 3:

    Combine the result from step 1 and step 2:

    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

    Step 4:

    Now, Order_by name then Group_By RowNumber.

    Using Min/Max, 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;

    **EDIT** I can see many asking why MIN or temp?

    temp - Since I created a temporary table inside the query, I have to give it an alise. It is a good practise.

    Why MIN in the select statement? Since some of us here may not be fimilar with sql, I'll start with where I left so you get the whole picture.

    1. Once you complete step 3, add "ORDER BY Name" (Refer above code on where to add Order by clause). The result will look like this https://imgur.com/aBHUqN6

    What changed? the names in all four columns are sorted as per alphabetical order.

    1. Now, we only want the names and not the NULL values from our virtual table. How can we do that? - There maybe be multiple ways, lets us consider the MIN/MAX (Yes, you can replace MIN with MAX and you will get the same result)

    2. Without GROUP BY clause - When a MIN/MAX is used in a Select statement, it will return The "LOWEST" element from each column (which happened to be the first element because we used ORDER BY, if you use MAX, you will get the last element from each column). It will look like this https://imgur.com/XDZzc4Z That means, you will always get a single row from a table.

    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) as temp
    1. With GROUP BY clause - The result set will have one row for EACH group (which is RowNumber in our case).

  • nasifra
     + 9 comments

    Here is my solution in MS SQL Server:

    SELECT
    [Doctor], [Professor], [Singer], [Actor]
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) [RowNumber], * FROM OCCUPATIONS
) AS tempTable
PIVOT
(
    MAX(NAME) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])
) AS pivotTable

  • nobuh
     + 8 comments
    -- MySQL
SELECT 
MIN(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,
MIN(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,
MIN(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,
MIN(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor
FROM (
  SELECT a.Occupation,
         a.Name,
         (SELECT COUNT(*) 
            FROM Occupations AS b
            WHERE a.Occupation = b.Occupation AND a.Name > b.Name) AS rank
  FROM Occupations AS a
) AS c
GROUP BY c.rank;

  • mannu_singhal
     + 8 comments

    *Trying without PIVOT *

    Select D.Name, P.Name, S.Name, A.Name

    from

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Doctor') D

    full outer join

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Professor') P on D.id = P.id

    full outer join

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Singer') S on P.id = S.id

    full outer join

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Actor') A on S.id = A.id;

Load more conversations