SQL实现竖线分隔数据拆分与转置的查询方案

不同SQL方言的实现方式略有差异，以下是主流数据库的解决方案：

MySQL #

适用于MySQL 8.0+（递归CTE） #

WITH RECURSIVE split_data AS (
    SELECT 
        UNKNOWN_COLUMN AS original_data,
        1 AS part_num,
        SUBSTRING_INDEX(UNKNOWN_COLUMN, '|', 1) AS split_value,
        SUBSTRING(UNKNOWN_COLUMN, LENGTH(SUBSTRING_INDEX(UNKNOWN_COLUMN, '|', 1)) + 2) AS remaining_data
    FROM your_table
    UNION ALL
    SELECT 
        original_data,
        part_num + 1,
        SUBSTRING_INDEX(remaining_data, '|', 1),
        SUBSTRING(remaining_data, LENGTH(SUBSTRING_INDEX(remaining_data, '|', 1)) + 2)
    FROM split_data
    WHERE remaining_data != ''
)
SELECT split_value AS transposed_column
FROM split_data;

递归逐段拆分每行的竖线分隔内容，最终提取所有拆分出的值作为新列。

适用于MySQL 5.x及以下版本（数字表关联） #

先创建一个包含连续数字的辅助表（数字范围覆盖数据中最多的分隔段数）：

-- 创建辅助数字表
CREATE TABLE numbers (num INT);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5); -- 根据实际需求调整数字数量

-- 拆分查询
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.UNKNOWN_COLUMN, '|', n.num), '|', -1) AS transposed_column
FROM your_table t
JOIN numbers n ON n.num <= LENGTH(t.UNKNOWN_COLUMN) - LENGTH(REPLACE(t.UNKNOWN_COLUMN, '|', '')) + 1
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(t.UNKNOWN_COLUMN, '|', n.num), '|', -1) != '';

PostgreSQL #

利用内置函数一步实现：

SELECT unnest(string_to_array(UNKNOWN_COLUMN, '|')) AS transposed_column
FROM your_table;

string_to_array将字符串转为数组，unnest把数组元素展开为多行，直接得到转置后的列。

SQL Server #

适用于SQL Server 2016+（STRING_SPLIT函数） #

SELECT value AS transposed_column
FROM your_table
CROSS APPLY STRING_SPLIT(UNKNOWN_COLUMN, '|')
WHERE value != '';

适用于SQL Server 2016以下版本（递归CTE+XML） #

WITH split_data AS (
    SELECT 
        UNKNOWN_COLUMN AS original_data,
        CAST('<v>' + REPLACE(UNKNOWN_COLUMN, '|', '</v><v>') + '</v>' AS XML) AS xml_data,
        1 AS part_num
    FROM your_table
    UNION ALL
    SELECT 
        original_data,
        xml_data,
        part_num + 1
    FROM split_data
    WHERE part_num < xml_data.value('count(/v)', 'INT')
)
SELECT xml_data.value('/v[sql:column("part_num")]', 'VARCHAR(MAX)') AS transposed_column
FROM split_data
WHERE xml_data.value('/v[sql:column("part_num")]', 'VARCHAR(MAX)') != '';

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