Split column in mysql table into muti column using query based on value in another column

Question

I have this table:

Id   name   degree
1    Ahmad  BS
2    John   MA
3    Abed   MA
4    Sami   DR
5    Mona   BS
6    Sara   BS

I want to split names based on degree so that the degree will be the header and any empty value in column will fill it with NULL

and the names in column will be sorted by Apathetically.

the result should be:

BS      MA    DR
Ahmad   Abed  Sami
Mona    John  NULL
Sara    NULL  NULL

What I tried:

SELECT 
  GROUP_CONCAT(if(degree = 'BS', name, NULL)) AS 'BS',
  GROUP_CONCAT(if(degree = 'MA', name, NULL)) AS 'MA',
  GROUP_CONCAT(if(degree = 'DR', name, NULL)) AS 'DR'
  FROM persons
  GROUP BY name;

But it seems not correct.

Although it is not impossible to do this purely in mysql, I would rather create the pivot on the application side. It will probably be a lot more effective. — Shadow, Aug 30 '16 at 06:31
Could you please help me to use pivot table to solve this issue? — Ayman Hussein, Aug 30 '16 at 06:34
@Shadow: you mentioned I can use pivot it's more effective "I would rather create the pivot on the application side." — Ayman Hussein, Aug 30 '16 at 06:39
Yeah, but you have not shared any info about your application, I do not know how you present the data to the user, so I cannot be more specific atm. — Shadow, Aug 30 '16 at 06:41
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Aug 30 '16 at 08:21

score 1 · Answer 1 · answered Mar 12 '17 at 18:57

SET @rn1 := 0, @rn2 := 0, @rn3 := 0;

SELECT MAX(bs) AS `BS`,
       MAX(ms) AS `MS`,
       MAX(dr) AS `DR`
FROM (
    SELECT CASE WHEN degree = 'BS' THEN @rn1 := @rn1 + 1
            WHEN degree = 'MA' THEN @rn2 := @rn2 + 1
            WHEN degree = 'DR' THEN @rn3 := @rn3 + 1
        END AS `Row Number`,
        IF(degree = 'BS', name, NULL) AS `bs`,
        IF(degree = 'MA', name, NULL) AS `ma`,
        IF(degree = 'DR', name, NULL) AS `dr`
    FROM persons
    ORDER BY name
) AS temp
GROUP BY `Row Number`

Since we want to put values from different rows in original table in the same row in the pivoted table, we can set up a separate row number counter for each column (in this case, three counters for three types of degrees), so we can place them all in the same row according to their row number.

In the temporary table, we list all the values in separate rows, and fill other cells in a row with NULL. We also increment the counters depending on the degree type. Back in the outer query, we can use MAX() (or MIN() ) function to display the only non-NULL value in that column for that particular row number.

I hope this helps someone. I was looking for an answer to an almost identical problem, and I decided to register on StackExchange when I finally found it, so I can answer this question. SE's community provided helpful solutions to me so many times, that it seemed only fair :)

StanislavL · Answer 2 · 2016-08-30T08:09:14.190

0

select subBS.name as BS,
       subMA.name as MA,
       subDR.name as DR
from (select @rn1:=0, @rn2:=0, @rn3:=0) rn,
    (select name, @rn1:=@rn1+1 as row
    from t
    where degree='BS') subBS
  full outer join
    (select name, @rn1:=@rn1+1 as row
    from t
    where degree='MA') subMA on subBS.row=subMA.row
  full outer join
    (select name, @rn1:=@rn1+1 as row
    from t
    where degree='DR') subDR  on subBS.row=subDR.row

One more attempt based on artificial subquery where amout of rows is bigger than in each subquery

We define subquery to number rows based on the table without restrictions and then skip empty records

select subBS.name as BS,
       subMA.name as MA,
       subDR.name as DR
from (select @rn:=0, @rn1:=0, @rn2:=0, @rn3:=0) rn,
    (select @rn:=@rn+1 as row
     from t) as main
  left outer join on main.row=subMA.row
    (select name, @rn1:=@rn1+1 as row
    from t
    where degree='BS'
    order by name) subBS
  left outer join
    (select name, @rn2:=@rn2+1 as row
    from t
    where degree='MA'
    order by name) subMA on main.row=subMA.row
  left outer join
    (select name, @rn3:=@rn3+1 as row
    from t
    where degree='DR'
    order by name) subDR on main.row=subDR.row
where subBS.name is not null 
   or subMA.name is not null 
   or subDR.name is not null

edited Aug 30 '16 at 08:09

answered Aug 30 '16 at 06:47

StanislavL

56,971
9
68
98

Mysql does not support full outer joins. – Shadow Aug 30 '16 at 06:52
@Shadow you are right. It's possible to emulate them http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql. Or we need somehow get max rows for each of the subqueries and place the first sub with max rows and use LEFT JOIN the other 2 subs – StanislavL Aug 30 '16 at 06:58
I believe if you increment a user defined variable in the outer query, then that will do the job, but you should test it. – Shadow Aug 30 '16 at 07:12
I got empty result! – Ayman Hussein Aug 30 '16 at 08:02
Corrected to use proper rn – StanislavL Aug 30 '16 at 08:11

Split column in mysql table into muti column using query based on value in another column

2 Answers2