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

|

2064 Discussions

|

  • konasanisaikris1
     + 0 comments

    WITH RankedNames AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum 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 RankedNames GROUP BY RowNum;

  • tranbaongochn_w1
     + 0 comments

    select max(case when Occupation = 'Doctor' then Name else null end) as Doctor, max(case when Occupation = 'Professor' then Name else null end) as Professor, max(case when Occupation = 'Singer' then Name else null end) as Singer, max(case when Occupation = 'Actor' then Name else null end) as Actor from( select Name, Occupation, row_number() over (partition by Occupation order by Name) as rn from OCCUPATIONS ) as x group by rn

  • x2912003x
     + 0 comments

    MS SQL SERVER SOLUTION USING PIVOT 

    WITH RankedNames 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 RankedNames
) AS SourceTable

PIVOT (
    MAX(Name) 
    FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor])
) AS PivotTable
ORDER BY rn;

  • lianasimon77
     + 0 comments

    WITH RankedNames AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum 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 RankedNames GROUP BY RowNum;

  • manishkumarsin27
     + 0 comments

    We can use below Solution for MySQL -

    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 RowNum FROM OCCUPATIONS ) AS RankedData GROUP BY RowNum ORDER BY RowNum;