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.
- Prepare
- SQL
- Advanced Select
- Occupations
- Discussions
Occupations
Occupations
Sort by
recency
|
1922 Discussions
|
Please Login in order to post a comment
with cte as ( select *, row_number()over(partition by occupation order by name ) row_num from occupations) select min(case when occupation = "doctor" then name else null end), min(case when occupation = "Professor" then name else null end), min(case when occupation = "Singer" then name else null end), min(case when occupation = "Actor" then name else null end)
from cte group by row_num
with Doctor as ( select name,occupation, row_number() over(order by name asc) as rn from occupations where occupation="Doctor" ), Actor as ( select name, occupation,row_number() over(order by name asc) as rn from occupations where occupation="Actor" ), Singer as ( select name, occupation,row_number() over(order by name asc) as rn from occupations where occupation="Singer" ), Professor as ( select name,occupation, row_number() over(order by name asc) as rn from occupations where occupation="Professor" )
select d.name,p.name,s.name,a.name from Professor p left join Singer s on s.rn=p.rn left join Doctor d on d.rn=p.rn left join Actor a on a.rn=p.rn
SET @max_rows := (SELECT COUNT(DISTINCT Occupation) FROM OCCUPATIONS);
SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor FROM ( SELECT Occupation, Name, row_num FROM ( SELECT Occupation, Name, @row_num := IF(@current_occupation = Occupation, @row_num + 1, 1) AS row_num, @current_occupation := Occupation FROM OCCUPATIONS ORDER BY Occupation, Name ) AS ranked ) AS pivoted GROUP BY row_num ORDER BY row_num;
For MS SQL SERVER:
with CTE AS ( select Name , Occupation , dense_rank() over (partition by Occupation order by Name) rn from OCCUPATIONS )
select [Doctor],[Professor],[Singer],[Actor] from (select Name , Occupation , rn from CTE) AS Source_table PIVOT ( Max(Name) FOR OCCUPATION IN ([Doctor],[Professor],[Singer ],[Actor]) ) AS P order by rn;
SELECT Doctor, Professor, Singer, Actor FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) as rn, name, occupation FROM occupations) PIVOT (MAX(name) FOR occupation IN ('Doctor' as Doctor,'Professor' as Professor, 'Singer' as Singer, 'Actor' as Actor)) ORDER BY rn;