无递增ID的MS SQL Server透视表实现问题求助

嘿，我懂你现在的感受——好久没碰SQL，刚上手透视就卡壳确实让人挠头！你猜的没错，当表中存在重复的分组键（比如你的id列都是11）时，直接用PIVOT很容易出问题，因为SQL没法区分同一组里的不同行，导致数据被覆盖或者结果不符合预期。咱们一步步来搞定它：

第一步：给分组内的行生成唯一序号 #

首先要给每个id分组里的行加上递增序号，用ROW_NUMBER()窗口函数就能轻松实现。这一步是关键，它能让SQL明确区分同一组内的不同记录：

SELECT 
    id, no1, no2, name, state,
    -- 按id分组，给每组内的行编序号（如果有特定排序需求，把ORDER BY里的(SELECT NULL)换成你需要的列，比如name或no1）
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL)) AS row_num
FROM 你的表名;

第二步：基于序号实现透视 #

接下来就可以用这个带序号的结果来做透视了。这里分两种常见场景给你举例：

场景1：已知要透视的列值（比如固定的name值） #

如果你的name值是固定的（比如只有alf、ben等），用CASE表达式配合聚合函数会更直观：

WITH numbered_rows AS (
    SELECT 
        id, no1, no2, name, state,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) AS row_num
    FROM 你的表名
)
SELECT 
    id, state,
    -- 把每个name对应的no1转成单独列
    MAX(CASE WHEN name = 'alf' THEN no1 END) AS alf_no1,
    MAX(CASE WHEN name = 'ben' THEN no1 END) AS ben_no1,
    -- 把每个name对应的no2转成单独列
    MAX(CASE WHEN name = 'alf' THEN no2 END) AS alf_no2,
    MAX(CASE WHEN name = 'ben' THEN no2 END) AS ben_no2
FROM numbered_rows
GROUP BY id, state;

场景2：未知要透视的列值（动态行数） #

如果同一id下的行数不确定，静态的PIVOT就不太够用了，先给你一个静态PIVOT的例子（假设同一id下最多2行）：

WITH numbered_rows AS (
    SELECT 
        id, state,
        -- 生成带序号的标识列，用来区分同一组内的不同行
        'row_' + CAST(ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) AS VARCHAR) AS row_id,
        name, no1, no2
    FROM 你的表名
)
SELECT 
    id, state,
    -- 透视出每行的name、no1、no2
    [row_1_name] AS first_name, [row_2_name] AS second_name,
    [row_1_no1] AS first_no1, [row_2_no1] AS second_no1,
    [row_1_no2] AS first_no2, [row_2_no2] AS second_no2
FROM (
    -- 把要透视的字段和行标识合并成新的列名
    SELECT 
        id, state,
        CONCAT(row_id, '_', col_name) AS pivot_col,
        col_value
    FROM numbered_rows
    UNPIVOT (
        col_value FOR col_name IN (name, no1, no2)
    ) AS unpvt
) AS src
PIVOT (
    MAX(col_value) FOR pivot_col IN (
        [row_1_name], [row_1_no1], [row_1_no2],
        [row_2_name], [row_2_no1], [row_2_no2]
    )
) AS pvt;

如果需要动态适配任意行数，你可以通过查询生成列名列表再拼接成动态SQL，要是需要的话咱们再细化~

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