如何使用SQL将逗号分隔列进行Unpivot/扁平化处理？

原表结构：

目标表结构：

根据你使用的数据库不同，拆分逗号分隔字符串的实现方式有所区别，以下是主流数据库的可行方案：

MySQL（8.0+） #

使用JSON_TABLE函数将字符串转为JSON数组后拆分：

SELECT 
    t.Location,
    j.Site
FROM 
    your_table t
JOIN 
    JSON_TABLE(
        CONCAT('["', REPLACE(t.Sites, ',', '","'), '"]'),
        '$[*]' COLUMNS (Site VARCHAR(255) PATH '$')
    ) j;

如果是MySQL 5.x版本（无JSON_TABLE），可借助数字辅助表实现：

-- 先创建数字辅助表（按需添加足够数量的数字）
CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5);

SELECT 
    t.Location,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.Sites, ',', n.n), ',', -1) AS Site
FROM 
    your_table t
JOIN 
    numbers n ON n.n <= LENGTH(t.Sites) - LENGTH(REPLACE(t.Sites, ',', '')) + 1
ORDER BY 
    t.Location, n.n;

SQL Server（2016+） #

使用STRING_SPLIT函数：

SELECT 
    t.Location,
    s.value AS Site
FROM 
    your_table t
CROSS APPLY 
    STRING_SPLIT(t.Sites, ',') s;

PostgreSQL #

使用STRING_TO_ARRAY结合UNNEST函数：

SELECT 
    t.Location,
    unnest(string_to_array(t.Sites, ',')) AS Site
FROM 
    your_table t;

Oracle #

使用REGEXP_SUBSTR结合层级查询：

SELECT 
    t.Location,
    TRIM(REGEXP_SUBSTR(t.Sites, '[^,]+', 1, LEVEL)) AS Site
FROM 
    your_table t
CONNECT BY 
    LEVEL <= REGEXP_COUNT(t.Sites, ',') + 1
    AND PRIOR t.Location = t.Location
    AND PRIOR SYS_GUID() IS NOT NULL;

注意：将上述代码中的your_table替换为你实际的表名。

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