I also encountered this problem on HackerRank. While I think the group_concat answer is very good and is typically used in these sorts of pivot situations with earlier versions of MySql, I find that concat and group_concat can be difficult to read and understand.
If your version of MySql supports window functions then you can solve this using temporary tables, as MySql does not support outer joins. You'll need a separate temp table for every pivot column to avoid Window function is not allowed in window specification errors:
use test;
drop table if exists occupations;
create table if not exists occupations (
name varchar(50)
,occupation varchar(50)
);
insert into occupations (name, occupation) select 'Samantha', 'Doctor'
union all select 'Julia', 'Actor'
union all select 'Maria', 'Actor'
union all select 'Meera', 'Singer'
union all select 'Ashley', 'Professor'
union all select 'Kelly', 'Professor'
union all select 'Christeen', 'Professor'
;
-- the way to approach this in mysql is to create a temp table with ordinals.
-- then upsert with four queries using row_number()
-- nb full join not supported. let's try temp table
drop table if exists doctors;
create temporary table doctors
(
name varchar(50)
,occupation varchar(50)
,ordinal int
);
insert into doctors
select
name
,occupation
,row_number() over (partition by occupation order by name) as ordinal
from occupations
where occupation = 'Doctor'
;
drop table if exists actors;
create temporary table actors
(
name varchar(50)
,occupation varchar(50)
,ordinal int
);
insert into actors
select
name
,occupation
,row_number() over (partition by occupation order by name) as ordinal
from occupations
where occupation = 'Actor'
;
drop table if exists professors;
create temporary table professors
(
name varchar(50)
,occupation varchar(50)
,ordinal int
);
insert into professors
select
name
,occupation
,row_number() over (partition by occupation order by name) as ordinal
from occupations
where occupation = 'Professor'
;
drop table if exists singers;
create temporary table singers
(
name varchar(50)
,occupation varchar(50)
,ordinal int
);
insert into singers
select
name
,occupation
,row_number() over (partition by occupation order by name) as ordinal
from occupations
where occupation = 'Singer'
;
-- upsert: update if not exists
drop table if exists results;
create temporary table results
(
singer varchar(50)
,actor varchar(50)
,doctor varchar(50)
,professor varchar(50)
,ordinal int primary key
);
insert into results (singer, ordinal)
select name, ordinal from singers
on duplicate key update singer = name
;
insert into results (actor, ordinal)
select name, ordinal from actors
on duplicate key update actor = name
;
insert into results (doctor, ordinal)
select name, ordinal from doctors
on duplicate key update doctor = name
;
insert into results (professor, ordinal)
select name, ordinal from professors
on duplicate key update professor = name
;
select singer, actor, doctor, professor from results;
Ps. I have to disagree with earlier comments: this is a pivot. We are projecting rows into columns, with the rows being a projection of occupations and ordinals.
