如何基于table1的非空param字段填充table2的theme_code字段

现有表结构 #

• table1：包含字段 call_id、param0 至 param30，所有param字段取值范围为1-100，允许为null
• table2：包含字段 call_id、theme_code，需从table1导入数据填充

任务要求 #

针对每个call_id，将其对应的每个非空param值作为theme_code生成一条记录；若param字段为null，则不生成对应记录。

示例 #

输入（table1） #

call_id   | param0 | param1 | param2 | param3 | param4 | param5 | param6 | param7 | param8 | param9 | param10 |
-------------------------------------------------------------------------------------------
1234567  | 24     | 2      | null   | 91     | 58     | null   | 25     | 19     | 77     | 62     | null    |

输出（table2） #

call_id   | theme_code |
------------------------
1234567  | 24         |
------------------------
1234567  | 2          |
------------------------
1234567  | 91         |
------------------------
1234567  | 58         |
------------------------
1234567  | 25         |
------------------------
1234567  | 19         |
------------------------
1234567  | 77         |
------------------------
1234567  | 62         |

解决方案 #

通用SQL写法（适用于所有数据库） #

通过UNION ALL逐个提取非空的param字段值，插入到table2中：

INSERT INTO table2 (call_id, theme_code)
SELECT call_id, param0 FROM table1 WHERE param0 IS NOT NULL
UNION ALL
SELECT call_id, param1 FROM table1 WHERE param1 IS NOT NULL
UNION ALL
SELECT call_id, param2 FROM table1 WHERE param2 IS NOT NULL
UNION ALL
SELECT call_id, param3 FROM table1 WHERE param3 IS NOT NULL
UNION ALL
SELECT call_id, param4 FROM table1 WHERE param4 IS NOT NULL
UNION ALL
SELECT call_id, param5 FROM table1 WHERE param5 IS NOT NULL
UNION ALL
SELECT call_id, param6 FROM table1 WHERE param6 IS NOT NULL
UNION ALL
SELECT call_id, param7 FROM table1 WHERE param7 IS NOT NULL
UNION ALL
SELECT call_id, param8 FROM table1 WHERE param8 IS NOT NULL
UNION ALL
SELECT call_id, param9 FROM table1 WHERE param9 IS NOT NULL
UNION ALL
SELECT call_id, param10 FROM table1 WHERE param10 IS NOT NULL
-- 依次添加param11到param30的SELECT语句，格式与上述一致
UNION ALL
SELECT call_id, param30 FROM table1 WHERE param30 IS NOT NULL;

支持UNPIVOT语法的数据库简化写法 #

SQL Server

INSERT INTO table2 (call_id, theme_code)
SELECT call_id, theme_code
FROM table1
UNPIVOT (
    theme_code FOR params IN (param0, param1, param2, ..., param30)
) AS unpvt;

Oracle

INSERT INTO table2 (call_id, theme_code)
SELECT call_id, theme_code
FROM table1
UNPIVOT INCLUDE NULLS (theme_code FOR params IN (param0, param1, ..., param30))
WHERE theme_code IS NOT NULL;

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