MySQL table "pivot" without creating tables/views: unique column values as header

Question

I have this 'occupations' two-column table, holding the name and occupation of multiple persons. The occupations are known and can only be 'Developer', 'Engineer','Doctor','Musician'.

Name   | Occupation
Dan    | Developer
Martin | Doctor
Sam    | Engineer
Andre  | Musician
Tom    | Engineer

The aim is to obtain something like the following:

Doctor | Engineer | Developer | Musician
Martin |    Sam   |   Dan     |  Andre
NULL   |    Tom   |   NULL    |   NULL

All columns should be alphabetically ordered.

Do you guys have any suggestions regarding how I can this be achieved (without creating tables, views) using MySQL?

Thanks a lot!

Possible duplicate of [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Derviş Kayımbaşıoğlu, Dec 30 '18 at 21:49
Totally agree with @RickJames. It's not only that the code is ugly, but it lacks flexibility. What would you do if there are more occupations than 4? How much code would you have to add? The only reason to do this in sql is maybe if you have a very large amount of data in the table. — forpas, Dec 31 '18 at 12:57
@forpas - And if there is a "very large amount" of data, then the output will be "very large". At that point, I would seriously criticize the UI decision to have 4 very tall lists. — Rick James, Dec 31 '18 at 23:48
@RickJames Don't you think that a large amount of data should be manipulated in-place and fetch only the result to the terminal (pc), as compared to fetch the whole data and do the filtering/processing there? — forpas, Dec 31 '18 at 23:52
@forpas - Sure. I was just digressing into UI issues. Here's another UI problem I have encountered when doing multiple columns: One column is "too short" and one is "too long"; they rarely balance in a pleasing way. At that point, I wonder if I should fold the longest column in two. Eventually, I a say "maybe the 4 column idea was 'bad'". — Rick James, Dec 31 '18 at 23:59

score 1 · Answer 1 · answered Dec 30 '18 at 22:40

This is a pain, but you can do it using variables and aggregation:

select max(doctor) as doctor,
       max(engineer) as engineer,
       max(developer) as developer,
       max(musician) as musician
from ((select name as doctor, null as engineer, null as developer, null as musician,
              (@rnd := @rnd + 1) as rn
       from t cross join
            (select @rnd := 0) as params
       where occupation = 'doctor'
      ) union all
      (select null as doctor, name as engineer, null as developer, null as musician,
              (@rne := @rne + 1) as rn
       from t cross join
            (select @rne := 0) as params
       where occupation = 'engineer'
      ) union all
      (select null as doctor, null as engineer, name as developer, null as musician,
              (@rnv := @rnv + 1) as rn
       from t cross join
            (select @rnv := 0) as params
       where occupation = 'developer'
      ) union all
      (select null as doctor, null as engineer, null as developer, name as musician,
              (@rnm := @rnm + 1) as rn
       from t cross join
            (select @rnm := 0) as params
       where occupation = 'musician'
      ) union all
     ) o
group by rn;

forpas · Answer 2 · 2018-12-30T23:53:50.677

This will work in MySql 8.0:

with occup as (
select
  case when o.occupation = 'Doctor' then o.Name end as Doctor, 
  case when o.occupation = 'Engineer' then o.Name end as Engineer,
  case when o.occupation = 'Developer' then o.Name end as Developer,  
  case when o.occupation = 'Musician' then o.Name end as Musician
from occupations o
),

doctors as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Doctor is null then 1 else 0 end
 ) as rn, occup.Doctor from occup
),
engineers as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Engineer is null then 1 else 0 end
 ) as rn, occup.Engineer from occup
),
developers as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Developer is null then 1 else 0 end
 ) as rn, occup.Developer from occup
),
musicians as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Musician is null then 1 else 0 end
 ) as rn, occup.Musician from occup
)

select doctors.Doctor, engineers.Engineer, developers.Developer, musicians.Musician 
from doctors
inner join engineers on doctors.rn = engineers.rn  
inner join developers on engineers.rn = developers.rn
inner join musicians on musicians.rn = developers.rn
WHERE coalesce(doctors.Doctor, engineers.Engineer, developers.Developer, musicians.Musician) IS NOT NULL;

See the demo

MySQL table "pivot" without creating tables/views: unique column values as header

2 Answers2