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

|

2186 Discussions

|

  • srishtirajput201
     + 1 comment

    SELECT COALESCE(MAX(CASE WHEN occupation = 'Doctor' THEN name END), 'NULL'), -- conditional aggregation COALESCE(MAX(CASE WHEN occupation = 'Professor' THEN name END), 'NULL'), -- inside the max function the statement only passes the name if it matches with the occupation otherwise it passes null COALESCE(MAX(CASE WHEN occupation = 'Singer' THEN name END), 'NULL'), COALESCE(MAX(CASE WHEN occupation = 'Actor' THEN name END), 'NULL') FROM ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn -- assigns the sequential row number to each row FROM OCCUPATIONS ) AS t GROUP BY rn -- groups the result by the row number ORDER BY rn; -- order the final result based on the row number

  • 26asingh04
     + 0 comments

    Can we have a dynamic query for this? Like what if we had more occupations?

  • smrutishah2610
     + 0 comments

    SELECT COALESCE(MAX(CASE WHEN occupation = 'Doctor' THEN name END), 'NULL'), COALESCE(MAX(CASE WHEN occupation = 'Professor' THEN name END), 'NULL'), COALESCE(MAX(CASE WHEN occupation = 'Singer' THEN name END), 'NULL'), COALESCE(MAX(CASE WHEN occupation = 'Actor' THEN name END), 'NULL') FROM ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) rn FROM OCCUPATIONS ) t GROUP BY rn ORDER BY rn;

  • simonjaramillor
     + 1 comment

    MySQL:

    WITH Ranked AS (
    SELECT name, occupation,
           ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn
    FROM OCCUPATIONS
),
Pivoted AS (
    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 Ranked
    GROUP BY rn
)
SELECT Doctor, Professor, Singer, Actor
FROM Pivoted;

  • mohd_kashif_ans1
     + 0 comments

    IN 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 Occupation, Name, ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name ASC) AS RowNum FROM OCCUPATIONS ) AS occ_rank GROUP BY RowNum ORDER BY RowNum;