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

|

2114 Discussions

|

  • srinivas_sk_vm
     + 0 comments

    with procte as ( select row_number()over(order by Professor) as rn, Professor from (select CASE WHEN OCCUPATION = 'Professor' THEN Name END Professor from OCCUPATIONS) as occ where Professor is NOT NULL), actcte as ( select row_number()over(order by Actor) as rn, Actor from (select CASE WHEN OCCUPATION = 'Actor' THEN Name END Actor from OCCUPATIONS) as occ where Actor is NOT NULL), singcte as ( select row_number()over(order by Singer) as rn, Singer from (select CASE WHEN OCCUPATION = 'Singer' THEN Name END Singer from OCCUPATIONS) as occ where Singer is NOT NULL), doccte as ( select row_number()over(order by Doctor) as rn, Doctor from (select CASE WHEN OCCUPATION = 'Doctor' THEN Name END Doctor from OCCUPATIONS) as occ where Doctor is NOT NULL) select Doctor, Professor, Singer, Actor from procte p left join actcte a on p.rn = a.rn left join singcte s on p.rn = s.rn left join doccte d on p.rn = d.rn;

  • arnak_b23
     + 1 comment

    --If anyone get stuck_by_this_problem-- SELECT [Doctor], [Professor], [Singer], [Actor] FROM(SELECT Occupation, Name, 'Name' + CAST(RANK() OVER( PARTITION BY Occupation ORDER BY Name) AS VARCHAR(10)) Occu_Rank FROM OCCUPATIONS) Tmp PIVOT (MAX(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor]) ) pvt ;

  • nguyenminhtrist
     + 0 comments

    SELECT [Doctor], [Professor], [Singer], [Actor] FROM ( select Name, Occupation, rank() over (partition by Occupation order by Name) as rank from OCCUPATIONS) a PIVOT ( MAX(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor]) ) AS PivotedOccupations

  • Xereng
     + 0 comments
    /*
Enter your query here.
*/

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 Name, Occupation, row_number() over (partition by Occupation order by Name) as rn
        from OCCUPATIONS
    
    )as sub
GROUP BY rn
order by rn;

  • mohslama02
     + 0 comments

    WITH RANKED AS( SELECT NAME,OCCUPATION, ROW_NUMBER()OVER(PARTITION BY OCCUPATION ORDER BY NAME) AS RN FROM OCCUPATIONS ) SELECT [DOCTOR],[PROFESSOR],[SINGER],[ACTOR] FROM(SELECT NAME,OCCUPATION,RN FROM RANKED) AS SOURCE_TAB PIVOT( MAX(NAME) FOR OCCUPATION IN ([DOCTOR],[PROFESSOR],[SINGER],[ACTOR]) )AS PIV_TAB ORDER BY RN;