SQL Server 数据透视问题：无中间列、无需聚合

嘿，这个问题我刚好碰到过，咱们一步步来解决它！

首先，你之前用STRING_SPLIT的问题在于默认情况下它不保证拆分后的顺序，但好在SQL Server 2022及以后的版本给STRING_SPLIT加了第三个参数enable_ordinal（设为1就能启用），这个参数会返回拆分后每个值的原始位置，完美解决顺序问题。

解决方案（SQL Server 2022+） #

直接利用带ordinal的STRING_SPLIT，结合行号和条件判断来转成你要的两列：

WITH SplitChanges AS (
    SELECT 
        job_id,
        change_id,
        VALUE AS change_value,
        -- 按job_id和change_id分组，按原始顺序生成行号
        ROW_NUMBER() OVER (PARTITION BY job_id, change_id ORDER BY ordinal) AS rn
    FROM change_table
    -- 第三个参数1启用ordinal，保证拆分顺序和原字符串一致
    CROSS APPLY STRING_SPLIT(change, CHAR(1), 1)
    -- 过滤掉拆分后产生的空字符串
    WHERE VALUE <> ''
)
SELECT 
    job_id AS [Job ID],
    change_id AS [Change ID],
    -- 取第1个非空值作为Change from
    MAX(CASE WHEN rn = 1 THEN change_value END) AS [Change from],
    -- 取第2个非空值作为Change to
    MAX(CASE WHEN rn = 2 THEN change_value END) AS [Change to]
FROM SplitChanges
GROUP BY job_id, change_id;

这里的MAX只是用来实现行转列的逻辑，没有做实际的聚合计算，完全符合你“无需聚合”的要求。

如果你用的是SQL Server 2022之前的版本 #

旧版本没有ordinal参数，咱们可以用XML拆分法来保证顺序：

WITH SplitChanges AS (
    SELECT 
        job_id,
        change_id,
        -- 提取拆分后的值并去空
        LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS change_value,
        -- 按原始顺序生成行号
        ROW_NUMBER() OVER (PARTITION BY job_id, change_id ORDER BY Split.a) AS rn
    FROM (
        -- 把原字符串转成XML格式，用CHAR(1)作为分隔符
        SELECT 
            job_id,
            change_id,
            CAST('<M>' + REPLACE(change, CHAR(1), '</M><M>') + '</M>' AS XML) AS Data
        FROM change_table
    ) AS A
    -- 拆分XML节点
    CROSS APPLY Data.nodes('/M') AS Split(a)
    -- 过滤空值
    WHERE LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) <> ''
)
SELECT 
    job_id AS [Job ID],
    change_id AS [Change ID],
    MAX(CASE WHEN rn = 1 THEN change_value END) AS [Change from],
    MAX(CASE WHEN rn = 2 THEN change_value END) AS [Change to]
FROM SplitChanges
GROUP BY job_id, change_id;

方案优势 #

• 不管用STRING_SPLIT(...,1)还是XML拆分，都能严格保留原字符串中值的顺序，确保第一个值是Change from，第二个是Change to
• 过滤掉了拆分后产生的空字符串，避免干扰结果
• 全程使用CTE和内置函数实现，没有创建中间表，完全符合你的需求

运行上面的查询后，就能得到你期望的结果啦！

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