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.
- Prepare
- SQL
- Advanced Select
- Occupations
- Discussions
Occupations
Occupations
Sort by
recency
|
1927 Discussions
|
Please Login in order to post a comment
SELECT * FROM ( SELECT s3.name as Doctor, s2.name as Professor, s4.name as Singer, s1.name as Actor FROM (SELECT rn, name FROM occ_n WHERE occ = 'Doctor') s3 LEFT JOIN (SELECT rn, name FROM occ_n WHERE occ = 'Professor') s2 ON s3.rn = s2.rn LEFT JOIN (SELECT rn, name FROM occ_n WHERE occ = 'Singer') s4 ON s3.rn = s4.rn LEFT JOIN (SELECT rn, name FROM occ_n WHERE occ = 'Actor') s1 ON s3.rn = s1.rn UNION SELECT s3.name as Doctor, s2.name as Professor, s4.name as Singer, s1.name as Actor FROM (SELECT rn, name FROM occ_n WHERE occ = 'Doctor') s3 RIGHT JOIN (SELECT rn, name FROM occ_n WHERE occ = 'Professor') s2 ON s3.rn = s2.rn RIGHT JOIN (SELECT rn, name FROM occ_n WHERE occ = 'Singer') s4 ON s3.rn = s4.rn RIGHT JOIN (SELECT rn, name FROM occ_n WHERE occ = 'Actor') s1 ON s3.rn = s1.rn ) x ORDER BY Doctor, Professor, Singer, Actor;
WITH occupation_row AS ( SELECT o.name , o.occupation , ROW_NUMBER() OVER (PARTITION BY o.occupation ORDER BY o.name ) row_num FROM occupations o ), doctor_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Doctor' ), actor_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Actor' ), professor_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Professor' ), singer_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Singer' )SELECT dc.name , pc.name , sc.name , ac.name FROM actor_cte ac FULL JOIN professor_cte pc on ac.row_num = pc.row_num FULL JOIN doctor_cte dc on ac.row_num = dc.row_num FULL JOIN singer_cte sc on ac.row_num = sc.row_num
WITH RankedOccupations 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 RankedOccupations GROUP BY rn ORDER BY rn;
SELECT MAX(CASE WHEN occupation = 'doctor' THEN name ELSE NULL END) AS doctor, MAX(CASE WHEN occupation = 'professor' THEN name ELSE NULL END) AS professor, MAX(CASE WHEN occupation = 'singer' THEN name ELSE NULL END) AS singer, MAX(CASE WHEN occupation = 'actor' THEN name ELSE NULL END) AS actor FROM ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn FROM occupations ) A GROUP BY rn ORDER BY rn;