Sunil Kumar Sunil Kumar - 4 years ago 122
MySQL Question

Divide the columns in mysql into multiple columns

I am completely new to mysql. Here I am trying to make a query in

mysql
which divides a column
col1
into 4 different columns based on its category(
col2
) in sorted order as shown below. I have written a query like this till now:

select if(category = 'first',name ,NULL) as first,
if(category = 'second',name,NULL) as second,
if(category = 'third',name,NULL) as third,
if(category = 'fourth',name,NULL) as fourth
from 'table';


this code gives me the four columns but I am stuck now as I am not able to further filter this.

Given table:

name category
John first
Sunil third
Jenny third
Ashley fourth
Meera second
Abhay first


Required answer:

col1 col2 col3 col4
Abhay Meera Jenny Ashley
John NULL Sunil NULL


Note that all the columns in the answer are sorted.

Answer Source

This is tricky. You are trying to stack lists vertically, rather than horizontally, which is not a "normal" SQL operation.

You can do what you want by using conditional aggregation. The problem is there is nothing to aggregate by. The solution is to introduce a variable column to calculate a sequence number for aggregation:

select max(case when category = 'first' then name end) as first,
       max(case when category = 'second' then name end) as second,
       max(case when category = 'third' then name end) as third,
       max(case when category = 'fourth' then name end) as fourth
from (select t.*,
             (@rn := if(@c = category, @rn + 1,
                        if(@c := category, 1, 1)
                       )
             ) as rn
      from `table` t cross join
           (select @c := '', @rn := 0) params
      order by category
     ) t
group by rn;

If you want the values in a particular order in each column, then add a second sort key after category.

EDIT:

I should note that if you don't need multiple rows, but just the values, you can concatenate them together:

select group_concat(case when category = 'first' then name end) as firsts,
       group_concat(case when category = 'second' then name end) as seconds,
       group_concat(case when category = 'third' then name end) as thirds,
       group_concat(case when category = 'fourth' then name end) as fourths
from`table` t
group by rn;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download