Sort by

recency

|

2141 Discussions

|

  • + 0 comments

    select max(case when t.occupation ='Doctor' then t.name end) as Doctor, max(case when t.occupation ='professor' then t.name end ) as professor, max(case when t.occupation ='singer' then t.name end) as singer, max(case when t.occupation ='actor' then t.name end) as actor from ( select occupation ,name , row_number() over (partition by occupation order by name) as rn from occupations ) as t group by rn

  • + 0 comments

    select max(IF(Occupation='Doctor',Name,NULL)) AS Doctor, MAX(IF(Occupation='Professor',Name,NULL)) as Professor, max(if(Occupation='Singer',Name,NULL)) as Singer, max(if(Occupation='Actor',Name,NULL)) as Actor from (select name,Occupation, ROW_NUMBER() OVER(PARTITION BY Occupation order by Name) as rn from OCCUPATIONS)as temp GROUP BY rn ORDER BY rn;

  • + 0 comments
    WITH doctors As (
        SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
        FROM OCCUPATIONS
        WHERE Occupation = 'Doctor'),
    professors AS (
        SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
        FROM OCCUPATIONS
        WHERE Occupation = 'Professor'),
    singers AS (
        SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
        FROM OCCUPATIONS
        WHERE Occupation = 'Singer'),
    actors AS (
        SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
        FROM OCCUPATIONS
        WHERE Occupation = 'Actor')
    
    SELECT d.Name, p.Name, s.Name, a.Name
    FROM Doctors AS d
    FULL OUTER JOIN Professors AS p ON d.rn = p.rn
    FULL OUTER JOIN Singers AS s ON p.rn = s.rn
    FULL OUTER JOIN Actors AS a ON s.rn = a.rn``
    
  • + 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