如何用SQL实现逗号分隔Tags数据的多行拆分（Unpivot）？

不同SQL数据库没有通用的跨平台现成函数，但各主流数据库都有动态适配的实现方式，无需提前知晓标签列表，能自动处理标签数量的变化：

MySQL/MariaDB（8.0+） #

使用递归CTE递归拆分字符串：

WITH RECURSIVE split_tags AS (
    SELECT 
        `Account Name`,
        SUBSTRING_INDEX(Tags, ',', 1) AS tag,
        SUBSTRING(Tags, LENGTH(SUBSTRING_INDEX(Tags, ',', 1)) + 2) AS remaining_tags
    FROM your_table
    WHERE Tags IS NOT NULL AND Tags != ''
    UNION ALL
    SELECT 
        `Account Name`,
        SUBSTRING_INDEX(remaining_tags, ',', 1) AS tag,
        SUBSTRING(remaining_tags, LENGTH(SUBSTRING_INDEX(remaining_tags, ',', 1)) + 2) AS remaining_tags
    FROM split_tags
    WHERE remaining_tags IS NOT NULL AND remaining_tags != ''
)
SELECT `Account Name`, tag
FROM split_tags;

SQL Server（2016+） #

直接使用内置的STRING_SPLIT函数：

SELECT 
    t.`Account Name`,
    s.value AS tag
FROM your_table t
CROSS APPLY STRING_SPLIT(t.Tags, ',') s
WHERE t.Tags IS NOT NULL AND t.Tags != '';

PostgreSQL #

通过STRING_TO_ARRAY转数组后用UNNEST拆分行：

SELECT 
    "Account Name",
    unnest(string_to_array(Tags, ',')) AS tag
FROM your_table
WHERE Tags IS NOT NULL AND Tags != '';

Oracle #

递归CTE方式（11g+） #

WITH split_tags AS (
    SELECT 
        "Account Name",
        REGEXP_SUBSTR(Tags, '[^,]+', 1, 1) AS tag,
        2 AS pos
    FROM your_table
    WHERE Tags IS NOT NULL AND Tags != ''
    UNION ALL
    SELECT 
        "Account Name",
        REGEXP_SUBSTR(Tags, '[^,]+', 1, pos) AS tag,
        pos + 1
    FROM split_tags
    WHERE REGEXP_SUBSTR(Tags, '[^,]+', 1, pos) IS NOT NULL
)
SELECT "Account Name", tag
FROM split_tags;

JSON_TABLE方式（12c+） #

SELECT 
    t."Account Name",
    j.tag
FROM your_table t,
     JSON_TABLE(
         '["' || REPLACE(t.Tags, ',', '","') || '"]',
         '$[*]' COLUMNS (tag VARCHAR2(100) PATH '$')
     ) j
WHERE t.Tags IS NOT NULL AND t.Tags != '';

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