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?
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 |
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With