- Practice
- SQL
- Advanced Select
- Occupations
- Discussions
Occupations
Occupations
+ 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 aftergroup 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
+ 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.
- 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.
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)
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
- With GROUP BY clause - The result set will have one row for EACH group (which is RowNumber in our case).
+ 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
+ 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;
+ 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;
Sort 876 Discussions, By:
Please Login in order to post a comment