By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table occupations as
select 'Samantha ' as name, 'Doctor' as occupation union all
select 'Julia ' as name, 'Actor' as occupation union all
select 'Maria ' as name, 'Actor' as occupation union all
select 'Meera ' as name, 'Singer' as occupation union all
select 'Ashely ' as name, 'Professor' as occupation union all
select 'Ketty ' as name, 'Professor' as occupation union all
select 'Christeen' as name, 'Professor' as occupation union all
select 'Jane ' as name, 'Actor' as occupation union all
select 'Jenny ' as name, 'Doctor' as occupation union all
select 'Priya ' as name, 'Singer' as occupation
Records: 10 Duplicates: 0 Warnings: 0
SELECT MAX(CASE occupation WHEN 'Doctor' THEN name END) AS Doctor,
MAX(CASE occupation WHEN 'Professor' THEN name END) AS Professor,
MAX(CASE occupation WHEN 'Singer' THEN name END) AS Singer,
MAX(CASE occupation WHEN 'Actor' THEN name END) AS Actor
FROM (SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY occupation) as seqnum
FROM occupations o
) o
GROUP BY seqnum;
| Doctor | Professor | Singer | Actor |
|---|---|---|---|
| Samantha | Ashely | Meera | Julia |
| Jenny | Ketty | Priya | Maria |
| null | Christeen | null | Jane |
SELECT MAX(CASE occupation WHEN 'Doctor' THEN name END) AS Doctor,
MAX(CASE occupation WHEN 'Professor' THEN name END) AS Professor,
MAX(CASE occupation WHEN 'Singer' THEN name END) AS Singer,
MAX(CASE occupation WHEN 'Actor' THEN name END) AS Actor
FROM (SELECT o.*,
(SELECT COUNT(*)
FROM occupations o2
WHERE o2.occupation = o.occupation AND
o2.name <= o.name
) as seqnum
FROM occupations o
) o
GROUP BY seqnum
ORDER BY seqnum;
| Doctor | Professor | Singer | Actor |
|---|---|---|---|
| Jenny | Ashely | Meera | Jane |
| Samantha | Christeen | Priya | Julia |
| null | Ketty | null | Maria |