Mysql Convert Column to row (Pivot table )

Question

I have a table like this

id	month	col1	col2	col3	col4
101	Jan	A	B	NULL	B
102	feb	C	A	G	E

And then I want to create report like this

desc	jan	feb
col1	A	C
col2	B	A
col3	0	G
Col4	B	E

Can anyone help with this?

Untruthful

Welcome to stackoverflow. This is a very common question. Please take a few minutes to search the archives. Try adapting one of the previous answers first. Then if you run into problems, post your query and any errors here. – Chemurgy

possible duplicate of MySQL pivot row into dynamic number of columns – Mihe

Answer 1

What you need to do is first, unpivot the data and then pivot it. But unfortunately MySQL does not have these functions so you will need to replicate them using a UNION ALL query for the unpivot and an aggregate function with a CASE for the pivot.

The unpivot or UNION ALL piece takes the data from your col1, col2, etc and turns it into multiple rows:

select id, month, col1 value, 'col1' descrip
from yourtable
union all
select id, month, col2 value, 'col2' descrip
from yourtable
union all
select id, month, col3 value, 'col3' descrip
from yourtable
union all
select id, month, col4 value, 'col4' descrip
from yourtable

See SQL Fiddle with Demo.

Result:

|  ID | MONTH |  VALUE | DESCRIP |
----------------------------------
| 101 |   Jan |      A |    col1 |
| 102 |   feb |      C |    col1 |
| 101 |   Jan |      B |    col2 |
| 102 |   feb |      A |    col2 |
| 101 |   Jan | (null) |    col3 |
| 102 |   feb |      G |    col3 |
| 101 |   Jan |      B |    col4 |
| 102 |   feb |      E |    col4 |

You then wrap this in a subquery to apply the aggregate and the CASE to convert this into the format you want:

select descrip, 
  max(case when month = 'jan' then value else 0 end) jan,
  max(case when month = 'feb' then value else 0 end) feb
from
(
  select id, month, col1 value, 'col1' descrip
  from yourtable
  union all
  select id, month, col2 value, 'col2' descrip
  from yourtable
  union all
  select id, month, col3 value, 'col3' descrip
  from yourtable
  union all
  select id, month, col4 value, 'col4' descrip
  from yourtable
) src
group by descrip

See SQL Fiddle with demo

The result is:

| DESCRIP | JAN | FEB |
-----------------------
|    col1 |   A |   C |
|    col2 |   B |   A |
|    col3 |   0 |   G |
|    col4 |   B |   E |

Haddington

Thank you very much. What if yourtable is a derived table came from a subquery. Am I going to to replace every yourtable with something like FROM (SELECT * FROM table WHERE name = 'condition') t1 ? – Culberson

@Accountantم Yes, that's exactly what you'd do – Haddington

Answer 2

Although this question is suuuper old and someone marked it as "very common", people still seem to find it (me included) and find it helpfull. I developed a more generalized version for unpivoting a row and thought it might be helpful to someone.

SET @target_schema='schema';
SET @target_table='table';
SET @target_where='`id`=1';
SELECT
    GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')
    INTO @sql
FROM (
    SELECT
        CONCAT('SELECT `id`,', QUOTE(COLUMN_NAME), ' AS `key`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry
    FROM (
        SELECT `COLUMN_NAME` 
        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
        WHERE `TABLE_SCHEMA`=@target_schema 
            AND `TABLE_NAME`=@target_table
    ) AS `A`
) AS `B`;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

I use this query on a MySQL 8.x server and aggregate it to a JSON object there, hence the id, key, value result structure.

Constanta

Answer 3

(Extending this great previous answer, as editing or commenting is not possible for my account at the moment)

Although this approach is not as straightforward as the UNION ALL / CASE - approach before, its advantage lies in that it can be used ("dynamically") for any number of original columns [please correct me on "any"].

A limitation that might lead to unclear errors is

the group_concat_max_len system variable, which has a default value of 1024

In that case, just try something like

SET SESSION group_concat_max_len = 92160;

Ca

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Theodosia

Answer 4

Assuming that the number of columns in your table is fixed and that the number of columns in the result table are also fixed, it is possible to combine your result with an auxiliary table just to replicate the data the number of times necessary so that it is possible to later transform the multiple rows in columns, example:

drop table if exists table1;
create table table1 (id int, month varchar(10), col1 char(1), col2 char(1), col3 char(1), col4 char(1));
insert into table1 (id, month, col1, col2, col3, col4) values 
(101, 'Jan', 'A', 'B', NULL, 'B'), 
(102, 'Feb', 'C', 'A', 'G', 'E'),
(103, 'Mar', 'X', 'Y', 'Z', 'T'),
(104, 'Apr', '1', '2', '3',  NULL)
;
select * from table1;

Your data is:

+------+-------+------+------+------+------+
| id   | month | col1 | col2 | col3 | col4 |
+------+-------+------+------+------+------+
|  101 | Jan   | A    | B    | NULL | B    |
|  102 | Feb   | C    | A    | G    | E    |
|  103 | Mar   | X    | Y    | Z    | T    |
|  104 | Apr   | 1    | 2    | 3    | NULL |
+------+-------+------+------+------+------+
4 rows in set (0,00 sec)

Then, create the auxiliary table with records necessary for the number of columns you want to transform:

create table amount (number int);
insert into amount (number) values (1), (2), (3), (4);

Now, just combine the results of both tables:

select
  amount.number,  
 
  case when number = 1 then 'col1' 
       when number = 2 then 'col2'
       when number = 3 then 'col3'
       when number = 4 then 'col4'
  end as description,

  case when number = 1 then group_concat(case when month = 'Jan' then col1 else '' end SEPARATOR '') 
       when number = 2 then group_concat(case when month = 'Jan' then col2 else '' end SEPARATOR '')
       when number = 3 then group_concat(case when month = 'Jan' then col3 else '' end SEPARATOR '')
       when number = 4 then group_concat(case when month = 'Jan' then col4 else '' end SEPARATOR '')
  end as jan,

  case when number = 1 then group_concat(case when month = 'Feb' then col1 else '' end SEPARATOR '') 
       when number = 2 then group_concat(case when month = 'Feb' then col2 else '' end SEPARATOR '')
       when number = 3 then group_concat(case when month = 'Feb' then col3 else '' end SEPARATOR '')
       when number = 4 then group_concat(case when month = 'Feb' then col4 else '' end SEPARATOR '')
  end as feb,

  case when number = 1 then group_concat(case when month = 'Mar' then col1 else '' end SEPARATOR '') 
       when number = 2 then group_concat(case when month = 'Mar' then col2 else '' end SEPARATOR '')
       when number = 3 then group_concat(case when month = 'Mar' then col3 else '' end SEPARATOR '')
       when number = 4 then group_concat(case when month = 'Mar' then col4 else '' end SEPARATOR '')
  end as mar,

  case when number = 1 then group_concat(case when month = 'Apr' then col1 else '' end SEPARATOR '') 
       when number = 2 then group_concat(case when month = 'Apr' then col2 else '' end SEPARATOR '')
       when number = 3 then group_concat(case when month = 'Apr' then col3 else '' end SEPARATOR '')
       when number = 4 then group_concat(case when month = 'Apr' then col4 else '' end SEPARATOR '')
  end as apr

from table1
cross join amount
group by amount.number
;

And the result is:

+--------+-------------+------+------+------+------+
| number | description | jan  | feb  | mar  | apr  |
+--------+-------------+------+------+------+------+
|      1 | col1        | A    | C    | X    | 1    |
|      2 | col2        | B    | A    | Y    | 2    |
|      3 | col3        |      | G    | Z    | 3    |
|      4 | col4        | B    | E    | T    |      |
+--------+-------------+------+------+------+------+
4 rows in set (0,00 sec)

Dorser