SQL实现单字段横向数据转纵向（逗号分隔ID字段）

嘿，这个需求我经常碰到——把逗号分隔的ID拆成单独行，同时保留其他字段的原值对吧？不同数据库的实现方式有点不一样，我给你整理了几种主流数据库的解决方案，你按需选用：

MySQL（8.0及以上版本） #

MySQL 8.0之后有两种比较方便的写法：
一种是用递归CTE来逐步拆分字符串：

WITH RECURSIVE split_ids AS (
    SELECT 
        YEAR, WEEK, BADGE, Name, remarks, BT_Hrs, NoOfDays,
        SUBSTRING_INDEX(ID, ',', 1) AS split_id,
        SUBSTRING(ID, LOCATE(',', ID) + 1) AS remaining_ids
    FROM your_table
    WHERE ID IS NOT NULL AND ID != ''
    UNION ALL
    SELECT 
        YEAR, WEEK, BADGE, Name, remarks, BT_Hrs, NoOfDays,
        SUBSTRING_INDEX(remaining_ids, ',', 1) AS split_id,
        SUBSTRING(remaining_ids, LOCATE(',', remaining_ids) + 1) AS remaining_ids
    FROM split_ids
    WHERE remaining_ids IS NOT NULL AND remaining_ids != ''
)
SELECT YEAR, WEEK, BADGE, Name, remarks, BT_Hrs, NoOfDays, split_id AS ID
FROM split_ids;

另一种是用JSON_TABLE，写法更简洁：

SELECT 
    t.YEAR, t.WEEK, t.BADGE, t.Name, t.remarks, t.BT_Hrs, t.NoOfDays,
    j.split_id AS ID
FROM your_table t
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(t.ID, ',', '","'), '"]'),
    '$[*]' COLUMNS (split_id VARCHAR(255) PATH '$')
) j
WHERE t.ID IS NOT NULL AND t.ID != '';

SQL Server（2016及以上版本） #

SQL Server自带的STRING_SPLIT函数可以直接搞定，非常省心：

SELECT 
    t.YEAR, t.WEEK, t.BADGE, t.Name, t.remarks, t.BT_Hrs, t.NoOfDays,
    s.value AS ID
FROM your_table t
CROSS APPLY STRING_SPLIT(t.ID, ',') s
WHERE t.ID IS NOT NULL AND t.ID != '';

如果是2016之前的旧版本，可能需要用递归CTE或者自定义函数，但优先推荐升级版本用STRING_SPLIT，效率更高。

PostgreSQL #

PostgreSQL可以用string_to_array配合unnest来拆分：

SELECT 
    YEAR, WEEK, BADGE, Name, remarks, BT_Hrs, NoOfDays,
    unnest(string_to_array(ID, ',')) AS ID
FROM your_table
WHERE ID IS NOT NULL AND ID != '';

或者用regexp_split_to_table，效果一样：

SELECT 
    YEAR, WEEK, BADGE, Name, remarks, BT_Hrs, NoOfDays,
    regexp_split_to_table(ID, ',') AS ID
FROM your_table
WHERE ID IS NOT NULL AND ID != '';

注意事项 #

• 记得把代码里的your_table替换成你实际的表名
• 我加了WHERE ID IS NOT NULL AND ID != ''的条件，避免ID为空时生成无效行
• 示例里的ID有前导零（比如000），所以拆分后的字段用字符串类型保留原格式，不要转成数值类型，否则前导零会丢失

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