不创建表/视图的mysql表“pivot”:作为标题的唯一列值

这将在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;

见 demo

这很痛苦,但您可以使用变量和聚合来完成:

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;