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

Sort by

recency

|

2043 Discussions

|

  • utkarsh_mms
     + 0 comments
    create view VV as (
 select case when occupation = 'Doctor' then name END As Doctor,
    case when occupation = 'P``rofessor' then name END As Professor,
    case when occupation = 'Singer' then name END As Singer,
    case when occupation = 'Actor' then name END As Actor, 
    
      ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS row_num from occupations);
    
    select max(Doctor), max(Professor), max(Singer), max(Actor) from VV group by row_num;

  • 160216737073_IT
     + 0 comments

    with cte as ( select *,ROW_NUMBER() over (partition by occupation order by name asc) as r from occupations ), doc as (select name,r from cte where occupation = 'Doctor'), prof as (select name,r from cte where occupation = 'Professor'), si as (select name,r from cte where occupation = 'Singer'), act as (select name,r from cte where occupation = 'Actor') select doc.name,prof.name,si.name,act.name from prof left join doc on prof.r=doc.r left join si on prof.r=si.r left join act on prof.r=act.r

  • muk_mandar
     + 0 comments

    Here's my disgraceful clunky code -

    with main_cte as
(select *,
case when occupation = 'Doctor' then name end as 'Doctor_column',
case when occupation = 'Professor' then name end as 'Professor_column',
case when occupation = 'Singer' then name end as 'Singer_column',
case when occupation = 'Actor' then name end as 'Actor_column',
row_number() over(partition by occupation order by Name) as 'id'
from occupations),
doc_cte as
(select id, Doctor_column from main_cte 
where occupation = 'Doctor'),
prof_cte as
(select id, Professor_column from main_cte 
where occupation = 'Professor'),
sing_cte as
(select id, Singer_column from main_cte 
where occupation = 'Singer'),
act_cte as
(select id, Actor_column from main_cte 
where occupation = 'Actor')
select 
doc.Doctor_column,
prof.Professor_column, 
sing.Singer_column,
act.Actor_column
from 
prof_cte prof left join sing_cte sing
on prof.id = sing.id
left join act_cte act
on prof.id = act.id
left join doc_cte doc
on prof.id = doc.id;

  • little_kitten_18
     + 0 comments

    The solution is at this Carbon link

  • huynhvu0911
     + 0 comments

    set @r_Doctor = 0, @r_Professor = 0,@r_Singer = 0,@r_Actor = 0; select min(Doctor),min(Professor),min(Singer),min(Actor) from( select case when OCCUPATION = 'Doctor' then (@r_Doctor:= @r_Doctor + 1) when OCCUPATION = 'Professor' then (@r_Professor:= @r_Professor + 1) when OCCUPATION = 'Singer' then (@r_Singer:= @r_Singer + 1) when OCCUPATION = 'Actor' then (@r_Actor:= @r_Actor + 1) end as row_num, case when OCCUPATION = 'Doctor' then name else NULL end as Doctor, case when OCCUPATION = 'Professor' then name else NULL end as Professor, case when OCCUPATION = 'Singer' then name else NULL end as Singer, case when OCCUPATION = 'Actor' then name else NULL end as Actor from OCCUPATIONS order by name ) as tbl group by row_num;