如何在T-SQL中将集合型列值拆分并展开为多行？

核心思路 #

因为列数较多（15列），手动编写所有列名效率极低，核心解法是用动态SQL自动生成拆分逻辑，通过集合的索引位置关联，将同一位置的元素组合成一行。

方案1：SQL Server（JSON数组格式） #

假设你的集合列是JSON字符串格式（如[1,2,3]），可以借助OPENJSON解析数组并获取元素索引，再通过动态SQL自动拼接所有列的处理逻辑：

步骤1：运行动态SQL脚本 #

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 自动获取表中所有列名，同时生成对应索引位置的JSON取值逻辑
SELECT @cols = STRING_AGG(
    CONCAT(
        'JSON_VALUE(t.[', QUOTENAME(c.name), '], ''$[', j.[key], ']'') AS ', QUOTENAME(c.name)
    ),
    ', '
)
FROM sys.columns c
CROSS JOIN (
    -- 从第一个集合列获取所有元素索引（假设所有集合长度一致）
    SELECT [key]
    FROM OPENJSON((SELECT TOP 1 [Column1] FROM YourTableName))
) j
WHERE c.object_id = OBJECT_ID('YourTableName')

-- 拼接最终执行的SQL语句
SET @sql = CONCAT(
    'SELECT ', @cols, '
     FROM YourTableName t
     CROSS JOIN (
         SELECT [key]
         FROM OPENJSON((SELECT TOP 1 [Column1] FROM YourTableName))
     ) j'
)

-- 执行生成的动态SQL
EXEC sp_executesql @sql

说明 #

• sys.columns自动读取表的所有列名，无需手动输入
• 基于第一个集合列的索引数量，自动生成所有列对应位置的元素提取逻辑
• 假设所有集合列的元素数量完全一致（你提到约1900个，符合要求）

方案2：PostgreSQL（原生数组类型） #

如果你的列是PostgreSQL原生数组类型（如integer[]、text[]），可以用generate_subscripts生成数组索引，再动态生成列的提取逻辑：

步骤1：运行动态SQL脚本 #

DO $$
DECLARE
    cols TEXT;
    sql TEXT;
BEGIN
    -- 自动获取表中所有列名，生成对应索引位置的数组取值逻辑
    SELECT string_agg(
        CONCAT(
            't.', quote_ident(c.column_name), '[s.i] AS ', quote_ident(c.column_name)
        ),
        ', '
    ) INTO cols
    FROM information_schema.columns c
    WHERE c.table_name = 'your_table_name'
      AND c.table_schema = 'public'; -- 根据你的实际schema调整

    -- 拼接最终执行的SQL语句
    sql := CONCAT(
        'SELECT ', cols, '
         FROM your_table_name t
         CROSS JOIN generate_subscripts((SELECT column1 FROM your_table_name LIMIT 1), 1) AS s(i)'
    );

    -- 执行生成的动态SQL
    EXECUTE sql;
END $$;

说明 #

• generate_subscripts基于第一个集合列的长度生成索引序列
• information_schema.columns自动读取表的所有列名，避免手动编写
• 通过[s.i]提取每列对应索引的元素，实现按位置拆分

注意事项 #

1. 必须确保所有集合列的元素数量完全一致，否则会出现数据错位或缺失
2. 如果是逗号分隔的字符串集合（如'1,2,3'），需先转换为JSON数组或拆分后关联索引，比如SQL Server可结合STRING_SPLIT和ROW_NUMBER()获取元素位置
3. 处理1900行规模的数据时，注意执行性能，优先使用原生数组/JSON解析函数

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