CODEWITHSUNDEEP
phpmysql
gtroop
gtroop

Reputation: 293

UNPIVOT mysql data to other table

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

Upvotes: 0

Views: 863

Answers (1)

clinomaniac
clinomaniac

Reputation: 2218

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.

Upvotes: 4

Related Questions