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.
Really good stuff. I translated your solution to something that works in MS SQL. These recursive functions you constructed {@r1:=@r1+1} is basically the same as creating a rank column partitioned by Occupation:
with cte as (
select RANK() OVER (PARTITION BY Occupation ORDER BY Name) as Rank,
case when Occupation='Doctor' then Name
else null
end as doctor,
case when Occupation='Professor' then Name
else null
end as prof,
case when Occupation='Singer' then Name
else null
end as singer,
case when Occupation='Actor' then Name
else null
end as actor
from Occupations)
select min(doctor), min(prof), min(singer), min(actor) from cte group by Rank
Occupations
You are viewing a single comment's thread. Return to all comments →
Really good stuff. I translated your solution to something that works in MS SQL. These recursive functions you constructed {@r1:=@r1+1} is basically the same as creating a rank column partitioned by Occupation:
with cte as ( select RANK() OVER (PARTITION BY Occupation ORDER BY Name) as Rank, case when Occupation='Doctor' then Name else null end as doctor, case when Occupation='Professor' then Name else null end as prof, case when Occupation='Singer' then Name else null end as singer, case when Occupation='Actor' then Name else null end as actor from Occupations) select min(doctor), min(prof), min(singer), min(actor) from cte group by Rank