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
The result is:
| DESCRIP | JAN | FEB |
-----------------------
| col1 | A | C |
| col2 | B | A |
| col3 | 0 | G |
| col4 | B | E |