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

|

2142 Discussions

|

  • SVVBathini
     + 0 comments

    For MySQL

    SELECT
        MIN(IF(occupation = "Doctor", name, NULL)) AS "Doctor",
        MIN(IF(occupation = "Professor", name, NULL)) AS "Professor",
        MIN(IF(occupation = "Singer", name, NULL)) AS "Singer",
        MIN(IF(occupation = "Actor", name, NULL)) AS "Actor"
FROM
(
        SELECT
                name,
                occupation,
                ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS indexing
        FROM occupations
) AS short_table
GROUP BY indexing;

  • vrahul5j
     + 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

  • yogeshyadav70560
     + 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;

  • sehgal_khwahish
     + 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``

  • pankajarora19
     + 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;