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

|

2016 Discussions

|

  • omarfaruq9723
     + 0 comments

    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 row_num FROM OCCUPATIONS ) GROUP BY row_num ORDER BY row_num;

  • Teerathep_ng
     + 0 comments
    SELECT
    MIN(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor,
    MIN(CASE WHEN occupation = 'Professor' THEN name END) AS Professor,
    MIN(CASE WHEN occupation = 'Singer' THEN name END) AS Singer,
    MIN(CASE WHEN occupation = 'Actor' THEN name END) AS Actor
--     You can use MIN or MAX up to you b/c It doesn't effect to the query (they are suppressed by GROUP BY rn)
FROM (SELECT
        name,
        occupation,
        ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS rn
     FROM occupations) AS t1
GROUP BY rn
ORDER BY rn;

  • ganeshcherupall1
     + 0 comments

    Mysql:- pivot is not working in mysql so use case statements, see below

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

  • carlaechi2410
     + 0 comments

    SQL Server

    with ect as ( select *, rank () over (partition by Occupation order by Name ) as Ranking from OCCUPATIONS ) select [Doctor],[Professor],[Singer],[Actor] from ect pivot ( max(Name) for Occupation in ([Doctor],[Professor],[Singer],[Actor])

    )as pivot_table order by case when [Doctor] is Null then 1 else 0 end, [Doctor] asc

  • iamyeri1017
     + 0 comments

    with PivotTable 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 PivotTable group by RowNum;

    the first step output:

    name | occupation | RowNum samatha doctor 1 julia actor 1 Maria actor 1 ...

    because the combinations of name and occupation is unique, so in the next step use Max( ) or Min( ) can choose the result and filter the NULL value.

    Doctor | Actor | Singer | Professor samantha ashley meera jane enny ketty null maria ...