CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 545

MySQL: Pivot-like query result

Given the following query, which produces a list of students and the cohorts to which they are assigned:

SELECT mdl_user.lastname, mdl_user.firstname, mdl_cohort.name
FROM mdl_cohort
INNER JOIN mdl_cohort_members ON mdl_cohort_members.cohortid = mdl_cohort.id
INNER JOIN mdl_user ON mdl_cohort_members.userid = mdl_user.id
ORDER BY mdl_user.lastname, mdl_cohort.name

Is it possible to produce a result that shows each cohort name ONCE, preferably as the column name, with each student assigned to that cohort listed below it? Something like this:

Engineering Cohort    Administrative Cohort    IT Cohort
------------------    ---------------------    ---------
John Doe              Jane Smith               Jane Doe
Jane Smith            John Smith               John Doe
Dan Jones             Dana Jones               Dana Jones

Upvotes: 2

Views: 97

Answers (2)

RMathis
RMathis

Reputation: 590

This can be scripted.. a purely SQL based approach is pretty ugly. The following approach loads results into a table (with a counter incremented from one for each) then joined together. The unions at the end emulate a full outer join to ensure no cohorts are left behind.

set @admin:=0;
set @eng:=0;
set @it:=0;

drop table if exists administrative;
create table administrative as
      select (@admin := @admin + 1) as counter
          , cohort
       from (    select concat(mdl_user.firstname, ' ', mdl_user.lastname) as cohort
                   from mdl_cohort
              inner join mdl_cohort_members on (mdl_cohort_members.cohortid = mdl_cohort.id)
              inner join mdl_user on (mdl_cohort_members.userid = mdl_user.id)
                  where mdl_cohort.name='Administrative Cohort' 
               order by mdl_user.lastname, mdl_user.firstname) as admin;

drop table if exists engineering;
create table engineering as
      select (@eng := @eng + 1) as counter
          , cohort
       from (    select concat(mdl_user.firstname, ' ', mdl_user.lastname) as cohort
                   from mdl_cohort
             inner join mdl_cohort_members on (mdl_cohort_members.cohortid = mdl_cohort.id)
             inner join mdl_user on (mdl_cohort_members.userid = mdl_user.id)
                  where mdl_cohort.name='Engineering Cohort' 
               order by mdl_user.lastname, mdl_user.firstname) as eng;

drop table if exists it;
create table it as
      select (@it := @it + 1) as counter
           , cohort
        from (    select concat(mdl_user.firstname, ' ', mdl_user.lastname) as cohort
                    from mdl_cohort
              inner join mdl_cohort_members on (mdl_cohort_members.cohortid = mdl_cohort.id)
              inner join mdl_user on (mdl_cohort_members.userid = mdl_user.id)
                   where mdl_cohort.name='IT Cohort' 
                order by mdl_user.lastname, mdl_user.firstname) as it;

select admin.cohort as `Administrative Cohorts`,
       eng.cohort   as `Engineering Cohorts`,
       it.cohort    as `IT Cohorts`
  from administrative admin
 left join engineering eng on(eng.counter=admin.counter)
 left join it on (it.counter=admin.counter)
 union
select ''         as `Administrative Cohorts`,
       eng.cohort as `Engineering Cohorts`,
       it.cohort  as `IT Cohorts`
  from engineering eng
 left join it on (it.counter=eng.counter)
 where not exists (select 1
                     from administrative 
                   where counter=eng.counter)
union
select ''         as `Administrative Cohorts`,
       ''         as `Engineering Cohorts`,
       it.cohort  as `IT Cohorts`
  from it
 where not exists (select 1
                     from administrative
                    where counter=it.counter)
   and not exists (select 1
                     from engineering
                    where counter=it.counter);

Upvotes: 1

Hatem Jaber
Hatem Jaber

Reputation: 2402

There are some really great examples here, thought this might help: Pivot table basics: rows to columns

Upvotes: 1

Related Questions