如何使用STRING_SPLIT与Pivot处理逗号分隔列并转周/日/小时维度

需求：对dbo.brut_data表中的「Σ Remaining Estimate」逗号分隔值列执行STRING_SPLIT拆分，再将结果Pivot转换为weeks、days、hours列，最终按sub_task_key和Assignee对数值分组。

示例数据 #

你已完成的拆分代码：

SELECT sub_task_key, [Assignee],  value
FROM dbo.brut_data
CROSS APPLY STRING_SPLIT([Σ Remaining Estimate], ',') AS s

完整解决方案 #

要实现Pivot转换，需先从拆分后的value中提取数值和对应单位（去除空格干扰），再通过Pivot完成列转换：

SELECT 
    sub_task_key,
    Assignee,
    [weeks],
    [days],
    [hours]
FROM (
    SELECT 
        sub_task_key,
        Assignee,
        -- 提取数值：截取空格前的内容并转为整数
        TRY_CAST(LEFT(LTRIM(value), CHARINDEX(' ', LTRIM(value)) - 1) AS INT) AS estimate_value,
        -- 提取单位：统一转为复数形式匹配目标列名
        CASE 
            WHEN LTRIM(value) LIKE '%week' THEN 'weeks'
            WHEN LTRIM(value) LIKE '%day' THEN 'days'
            WHEN LTRIM(value) LIKE '%hour' THEN 'hours'
            ELSE LTRIM(RIGHT(value, LEN(value) - CHARINDEX(' ', LTRIM(value))))
        END AS estimate_unit
    FROM dbo.brut_data
    CROSS APPLY STRING_SPLIT([Σ Remaining Estimate], ',') AS s
) AS split_data
PIVOT (
    MAX(estimate_value)
    FOR estimate_unit IN ([weeks], [days], [hours])
) AS pivot_result
ORDER BY sub_task_key;

期望结果 #

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