基于行号创建自定义列：SQL行转列（Pivot）实现咨询

固定列数的静态PIVOT方案 #

如果提前知道拆分后的元素数量（比如示例里是6个），可以直接用静态PIVOT完成转换，同时简化原查询的嵌套结构：

SELECT 
    [1] AS label1,
    [2] AS label2,
    [3] AS label3,
    [4] AS label4,
    [5] AS label5,
    [6] AS label6
FROM (
    -- 直接生成行号和拆分后的值，减少嵌套层级
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN, 
        value
    FROM STRING_SPLIT('1.16.8.20220812.2.961', '.')
) t
PIVOT (
    -- 每个行号对应唯一值，MAX/MIN聚合结果一致
    MAX(value)
    -- 指定要透视的行号列，并映射到自定义列名
    FOR RN IN ([1], [2], [3], [4], [5], [6])
) pvt;

动态列数的动态SQL方案 #

如果拆分后的元素数量不固定，需要自动适配列数，可以用动态SQL生成PIVOT语句：

DECLARE @str NVARCHAR(MAX) = '1.16.8.20220812.2.961';
DECLARE @cols NVARCHAR(MAX);
DECLARE @pivot_cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成自定义列名（label1, label2...）和对应的行号标识
SELECT 
    @cols = STRING_AGG(QUOTENAME(RN) + ' AS ' + QUOTENAME('label' + CAST(RN AS NVARCHAR)), ', '),
    @pivot_cols = STRING_AGG(QUOTENAME(RN), ', ')
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
    FROM STRING_SPLIT(@str, '.')
) t;

-- 拼接完整的动态SQL语句
SET @sql = N'
SELECT ' + @cols + N'
FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN, 
        value
    FROM STRING_SPLIT(@str, ''.'')
) t
PIVOT (
    MAX(value)
    FOR RN IN (' + @pivot_cols + N')
) pvt;';

-- 执行动态SQL
EXEC sp_executesql @sql, N'@str NVARCHAR(MAX)', @str = @str;

更可靠的顺序保证（SQL Server 2022+） #

SQL Server 2022及以上版本中，STRING_SPLIT支持第三个参数enable_ordinal，可以直接获取拆分元素的原生顺序，避免ORDER BY (SELECT NULL)可能带来的顺序不确定性：

SELECT 
    [1] AS label1,
    [2] AS label2,
    [3] AS label3,
    [4] AS label4,
    [5] AS label5,
    [6] AS label6
FROM (
    -- 直接使用ordinal作为行号，保证顺序和原字符串一致
    SELECT value, ordinal AS RN
    FROM STRING_SPLIT('1.16.8.20220812.2.961', '.', 1)
) t
PIVOT (
    MAX(value)
    FOR RN IN ([1], [2], [3], [4], [5], [6])
) pvt;

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