T-SQL将逗号分隔的列转换为多列-动态

-- 创建一个示例表
CREATE TABLE ExampleTable (
    ID INT,
    CommaSeparatedValues VARCHAR(MAX)
)

-- 插入示例数据
INSERT INTO ExampleTable (ID, CommaSeparatedValues)
VALUES (1, 'A,B,C,D'),
       (2, 'E,F,G'),
       (3, 'H,I,J,K,L')

-- 使用动态SQL将逗号分隔的列转换为多列
DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)

-- 获取所有可能的列名
SELECT @Columns = COALESCE(@Columns + ',', '') + QUOTENAME(value)
FROM (
    SELECT DISTINCT value
    FROM ExampleTable
    CROSS APPLY STRING_SPLIT(CommaSeparatedValues, ',')
) AS SplitValues

-- 构建动态SQL语句
SET @DynamicSQL = N'
SELECT ID, ' + @Columns + '
FROM (
    SELECT ID, value, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS RowNum
    FROM ExampleTable
    CROSS APPLY STRING_SPLIT(CommaSeparatedValues, ',')
) AS SplitValues
PIVOT (
    MAX(value)
    FOR RowNum IN (' + @Columns + ')
) AS PivotTable'

-- 执行动态SQL语句
EXEC sp_executesql @DynamicSQL

-- 删除示例表
DROP TABLE ExampleTable