针对你要把monthly宽表的数据转换为monthlydata窄表的需求，因为MySQL没有原生的UNPIVOT函数，我们可以用UNION ALL来实现行转列的效果，下面提供两种实用方案：

方案一：动态处理整张表的数据（推荐） #

如果你的monthly表有多条记录，这个方案可以一次性把所有有效月份的数据转换并插入到目标表中：

INSERT INTO `monthlydata` (`year`, `monthName`, `stat_id`, `cat_id`, `data`)
SELECT
  `year`,
  month_name,
  `stat_id`,
  `cat_id`,
  data_value
FROM (
  -- 处理1月数据
  SELECT
    `year`,
    'January' AS month_name,
    `stat_id`,
    `cat_id`,
    `January` AS data_value
  FROM `monthly`
  WHERE `January` != ''
  UNION ALL
  -- 处理2月数据
  SELECT
    `year`,
    'February' AS month_name,
    `stat_id`,
    `cat_id`,
    `February` AS data_value
  FROM `monthly`
  WHERE `February` != ''
  UNION ALL
  -- 处理3月数据
  SELECT
    `year`,
    'March' AS month_name,
    `stat_id`,
    `cat_id`,
    `March` AS data_value
  FROM `monthly`
  WHERE `March` != ''
  UNION ALL
  -- 处理4月数据
  SELECT
    `year`,
    'April' AS month_name,
    `stat_id`,
    `cat_id`,
    `April` AS data_value
  FROM `monthly`
  WHERE `April` != ''
  UNION ALL
  -- 处理5月数据
  SELECT
    `year`,
    'May' AS month_name,
    `stat_id`,
    `cat_id`,
    `May` AS data_value
  FROM `monthly`
  WHERE `May` != ''
  UNION ALL
  -- 处理6月数据
  SELECT
    `year`,
    'June' AS month_name,
    `stat_id`,
    `cat_id`,
    `June` AS data_value
  FROM `monthly`
  WHERE `June` != ''
  UNION ALL
  -- 处理7月数据
  SELECT
    `year`,
    'July' AS month_name,
    `stat_id`,
    `cat_id`,
    `July` AS data_value
  FROM `monthly`
  WHERE `July` != ''
  UNION ALL
  -- 处理8月数据
  SELECT
    `year`,
    'August' AS month_name,
    `stat_id`,
    `cat_id`,
    `August` AS data_value
  FROM `monthly`
  WHERE `August` != ''
  UNION ALL
  -- 处理9月数据
  SELECT
    `year`,
    'September' AS month_name,
    `stat_id`,
    `cat_id`,
    `September` AS data_value
  FROM `monthly`
  WHERE `September` != ''
  UNION ALL
  -- 处理10月数据
  SELECT
    `year`,
    'October' AS month_name,
    `stat_id`,
    `cat_id`,
    `October` AS data_value
  FROM `monthly`
  WHERE `October` != ''
  UNION ALL
  -- 处理11月数据
  SELECT
    `year`,
    'November' AS month_name,
    `stat_id`,
    `cat_id`,
    `November` AS data_value
  FROM `monthly`
  WHERE `November` != ''
  UNION ALL
  -- 处理12月数据
  SELECT
    `year`,
    'December' AS month_name,
    `stat_id`,
    `cat_id`,
    `December` AS data_value
  FROM `monthly`
  WHERE `December` != ''
) AS unpivoted_data;

关键点说明： #

• 使用UNION ALL代替UNION，避免不必要的去重操作，大幅提升处理效率。
• 每个子查询通过WHERE 月份列 != ''过滤空值数据，只插入有实际内容的记录。
• 如果monthlydata的id是自增主键，不需要在INSERT语句中指定id列，数据库会自动生成连续的id值。如果需要手动控制id顺序，可以在SELECT中加入ROW_NUMBER() OVER (ORDER BY month_name)来生成有序的id。

方案二：手动生成单条记录的INSERT语句 #

如果只是处理你给出的单条测试数据，直接写静态的INSERT语句更简单直接：

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');

这个方案适合少量数据的快速转换，不需要动态查询逻辑。

内容的提问来源于stack exchange，提问作者gtroop