Microsoft SQL环境下限制列数的Pivot行转列换行实现问题

实现思路 #

• 对所有序列号按业务规则排序后生成全局连续序号
• 基于全局序号计算两个维度的值：
  • 行分组ID：通过(全局序号 - 1) / 10的整数除法实现每10个序列号归为同一行
  • 列标识：每个行分组内的序号固定为1-10，对应模板要求的Product01到Product10列，超出10的序号会自动归到下一个行分组的前列位置
• 最终PIVOT时固定使用10个列名，不会生成额外列，超出10个的序列号自动换行展示

完整实现代码 #

CREATE TABLE #sourceTable(SerialNo VARCHAR(20))

INSERT INTO #sourceTable
(SerialNo)
VALUES
    ('BK018001B6'),
    ('BK018001B7'),
    ('BK018001B8'),
    ('BK018001B9'),
    ('BK018001BB'),
    ('BK018001BC'),
    ('BK018001BD'),
    ('BK018001BF'),
    ('BK018001BG'),
    ('BK018001BH'),
    ('BK018001BJ'),
    ('BK018001BK'),
    ('BK018001BL')

-- 核心处理逻辑
;WITH numbered_serial AS (
    -- 第一步：生成全局连续序号，排序规则可按业务调整，如原逻辑的ORDER BY p.id
    SELECT 
        SerialNo,
        ROW_NUMBER() OVER(ORDER BY SerialNo) AS global_rn
    FROM #sourceTable
),
grouped_serial AS (
    -- 第二步：计算行分组和列标识
    SELECT
        SerialNo,
        (global_rn - 1) / 10 AS row_group_id, -- 每10个为一个行分组
        -- 生成固定的Product01~Product10列名
        'Product' + RIGHT('00' + CAST(CASE WHEN global_rn % 10 = 0 THEN 10 ELSE global_rn % 10 END AS VARCHAR(2)), 2) AS product_col
    FROM numbered_serial
)
-- 第三步：行转列，固定10列
SELECT 
    Product01,Product02,Product03,Product04,Product05,
    Product06,Product07,Product08,Product09,Product10
FROM grouped_serial
PIVOT (
    MAX(SerialNo)
    FOR product_col IN (Product01,Product02,Product03,Product04,Product05,Product06,Product07,Product08,Product09,Product10)
) AS pivot_result
ORDER BY row_group_id;

DROP TABLE #sourceTable

结果验证 #

针对提供的13条测试数据，执行后会返回2行结果：

• 第一行：Product01~Product10依次填充前10条序列号
• 第二行：Product01_{Product03填充第11}13条序列号，剩余7列为空
  完全匹配客户模板要求。

业务适配说明 #

如果实际场景中需要按其他维度拆分（比如同一个订单的序列号单独分组换行），只需在ROW_NUMBER()的OVER子句中增加PARTITION BY 业务维度字段即可。

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