Mysql Convert Column to row (Pivot table )
Solution 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
The result is:
| DESCRIP | JAN | FEB |
-----------------------
| col1 | A | C |
| col2 | B | A |
| col3 | 0 | G |
| col4 | B | E |
Solution 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.
Related videos on Youtube
user1914516
Updated on July 09, 2022Comments
-
user1914516 almost 2 years
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?
-
Leigh over 11 yearsWelcome 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.
-
RichardTheKiwi about 11 yearspossible duplicate of MySQL pivot row into dynamic number of columns
-
-
Moe Chughtai about 8 yearsThank you for this! Super helpful.
-
Accountant م almost 7 yearsThank you very much. What if
yourtable
is a derived table came from a subquery. Am I going to to replace everyyourtable
with something likeFROM (SELECT * FROM table WHERE name = 'condition') t1
? -
Taryn almost 7 years@Accountantم Yes, that's exactly what you'd do
-
Ishpreet over 4 years@Taryn - Your solution is amazing. This helped me a lot. Voted for your amazing answer.