Sort by

recency

|

2138 Discussions

|

  • + 0 comments

    My Solution:

    with t1 as ( select distinct name, 
            row_number() over (order by name) id
                from occupations
                where lower(occupation) = 'doctor'
                ),
    t2 as  ( select distinct name, 
            row_number() over (order by name) id
                from occupations
                where lower(occupation) = 'professor'
                ),  
    t3 as  ( select distinct name, 
            row_number() over (order by name) id
                from occupations
                where lower(occupation) = 'singer'
                ),  
    t4 as  ( select distinct name, 
            row_number() over (order by name) id
                from occupations
                where lower(occupation) = 'actor'
                )
    select distinct t1.name, t2.name, t3.name, t4.name
    from t1
    full outer join t2
    on t1.id = t2.id
    full outer join t3
    on t2.id = t3.id
    full outer join t4
    on t3.id = t4.id
    order by 1, 2, 3, 4;
    
  • + 0 comments
    SELECT [Doctor], [Professor], [Singer], [Actor]
    
    
    
      FROM 
    
    
    
      (SELECT [name]
          ,[occupation]
    	  ,ROW_NUMBER() over (PARTITION by [occupation] order by [name] ) as rn
      FROM [OCCUPATIONS] ) OC
    
      PIVOT (
      
      max([name])
    for [occupation] in ([Doctor], [Professor], [Singer], [Actor])
    

    ) as PO

  • + 0 comments

    My solutions (MySQL). hope it helps!

    First approach: using CTE

    WITH occupation_rank AS (
            SELECT
                    name,
                    occupation,
                    ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name ASC) as rn
            FROM occupations
    )
    SELECT
            MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS doctor_names,
            MAX(CASE WHEN occupation = 'Professor' THEN name END) AS professor_names,
            MAX(CASE WHEN occupation = 'Singer' THEN name END) AS singer_names,
            MAX(CASE WHEN occupation = 'Actor' THEN name END) AS actor_names 
    FROM occupation_rank
    GROUP BY rn
    ORDER BY rn;
    

    Second approach: using subquery

    SELECT
            MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS doctor_names,
            MAX(CASE WHEN occupation = 'Professor' THEN name END) AS professor_names,
            MAX(CASE WHEN occupation = 'Singer' THEN name END) AS singer_names,
            MAX(CASE WHEN occupation = 'Actor' THEN name END) AS actor_names
    FROM (
            SELECT
                    name,
                    occupation,
                    ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) as rn
            FROM OCCUPATIONS
    ) AS sorted
    GROUP BY rn;
    
  • + 0 comments
    SELECT 
        [Doctor],
        [Professor],
        [Singer],
        [Actor]
    FROM (
        SELECT 
            [Name],
            [Occupation],
            ROW_NUMBER() OVER(
                PARTITION BY [Occupation] ORDER BY [Name]
            ) AS rowNumber
        FROM OCCUPATIONS
    ) AS SourceTable
    PIVOT (
        MAX([NAME])
        FOR [Occupation] IN (
            Doctor,
            Professor,
            Singer,
            Actor
        )
    ) AS PivotTable;
    
  • + 1 comment

    MySQL: with ranked as (select name, occupation, row_number() over (partition by occupation order by name) as rn 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 Ranked Group by rn Order by rn