UNPIVOT mysql data to other table

Question

Hi i have the following mysql data

INSERT INTO `monthly` (`id`, `year`, `stat_id`, `cat_id`, `January`, `February`, `March`, `April`, `May`, `June`, `July`, `August`, `September`, `October`, `November`, `December`) VALUES
(1, '2017', '12', '25', '1', '3', '1', '1', '3', '4', '4', '2', '4', '', '', ''),

and i would like it to be convert to be like this

INSERT INTO `monthlydata` (`id`, `year`, `monthName`, `stat_id`, `cat_id`, `data`) VALUES
(1, '2017', 'January', '12', '25', '1'),
(2, '2017', 'February', '12', '25', '3'),
(3, '2017', 'March', '12', '25', '1'),
(4, '2017', 'April', '12', '25', '1'),
(5, '2017', 'May', '12', '25', '3'),
(6, '2017', 'June', '12', '25', '4'),
(7, '2017', 'July', '12', '25', '4'),
(8, '2017', 'August', '12', '25', '2'),
(9, '2017', 'September', '12', '25', '4'),
(10, '2017', 'October', '12', '25', ''),
(11, '2017', 'November', '12', '25', ''),
(12, '2017', 'December', '12', '25', ''),

is there an easier way to do this using mysql/php

[INSERT INTO .. SELECT FROM](https://dev.mysql.com/doc/refman/5.7/en/insert-select.html) — ArtisticPhoenix, Apr 03 '18 at 06:01
do you want to transform the `INSERT INTO` string using PHP or your existing MySQL data? — BNT, Apr 03 '18 at 06:01
After looking at your table, I doubt it's possible with just Mysql not in one query at least. Because you have to pivot the table. Well you could do 12 Unions, but that's less then Ideal... lol — ArtisticPhoenix, Apr 03 '18 at 06:06
You can copy the table like this : [link](https://stackoverflow.com/questions/2343644/in-mysql-how-to-copy-the-content-of-one-table-to-another-table-within-the-same) — Man, Apr 03 '18 at 06:20

clinomaniac · Accepted Answer · 2018-04-03T06:15:12.730

You need to UNPIVOT your data. MySQL doesn't have a built in function to do that so you'll need to use multiple queries.

INSERT INTO `monthlydata` (`id`, `year`, `monthName`, `stat_id`, `cat_id`, `data`) VALUES
SELECT id, year, 'January', stat_id, cat_id, January
    FROM monthly WHERE monthName = 'January'
UNION ALL 
SELECT id, year, 'February', stat_id, cat_id, February
    FROM monthly WHERE monthName = 'February'
UNION ALL 
SELECT id, year, 'March', stat_id, cat_id, March
    FROM monthly WHERE monthName = 'March'
.....

ID column here might cause issues. Depending on how you have defined it. If it is auto generated then you can remove it from the INSERT and let it be auto generated. Since you'll have rows for all months with same ID, you need to handle that scenario.

UNPIVOT mysql data to other table

1 Answers1

Linked

Related