Oracle 10g中按CODBULTO分组，将NUMEROSERIE每5个转为列的方法

有一张名为XIPNMVSCHECK的表，核心列包括标识箱子的CODBULTO和标识产品序列号的NUMEROSERIE。需要编写SQL查询，将每个箱子的序列号按每行5个的格式展开，输出CODBULTO及SSCC1至SSCC5列，示例如下（以CODBULTO=35且有10个序列号为例）：

CODBULTO  SSCC1  SSCC2  SSCC3  SSCC4  SSCC5
   35      265    652    452    656    125
   35      145    489    978    842    546

用户尝试的代码及问题 #

尝试了以下SQL，但仅能正确生成第一行数据，后续行的SSCC列均为NULL：

SELECT T1.CODBULTO,
MAX(CASE WHEN T1.POS = 1 THEN T1.NUMEROSERIE END) AS SSCC1,
MAX(CASE WHEN T1.POS = 2 THEN T1.NUMEROSERIE END) AS SSCC2,
MAX(CASE WHEN T1.POS = 3 THEN T1.NUMEROSERIE END) AS SSCC3,
MAX(CASE WHEN T1.POS = 4 THEN T1.NUMEROSERIE END) AS SSCC4,
MAX(CASE WHEN T1.POS = 5 THEN T1.NUMEROSERIE END) AS SSCC5
FROM (SELECT X.CODBULTO,
        X.NUMEROSERIE,
        (SELECT COUNT(DISTINCT X2.NUMEROSERIE)
         FROM XIPNMVSCHECK X2
         WHERE X2.CODBULTO = X.CODBULTO
           AND X2.NUMEROSERIE <= X.NUMEROSERIE) AS POS
    FROM XIPNMVSCHECK X) T1
GROUP BY T1.CODBULTO, FLOOR((POS - 1) / 5)
ORDER BY T1.CODBULTO, FLOOR((POS - 1) / 5);

错误结果：

CODBULTO  SSCC1  SSCC2  SSCC3  SSCC4  SSCC5
   35      265    652    452    656    125
   35      NULL   NULL   NULL   NULL   NULL

问题原因：原查询中的POS是每个箱子下序列号的全局序号（比如第6个序列号的POS=6），但CASE判断的是POS=1-5，第二组（序号6-10）的记录都不满足这些条件，因此返回NULL。需要计算每组内的相对位置，而非全局序号。

解决方案 #

方案1：兼容Oracle 10g版本 #

使用ROW_NUMBER()为每个箱子下的序列号生成连续序号，再计算组号和组内位置：

SELECT 
    CODBULTO,
    MAX(CASE WHEN GROUP_POS = 1 THEN NUMEROSERIE END) AS SSCC1,
    MAX(CASE WHEN GROUP_POS = 2 THEN NUMEROSERIE END) AS SSCC2,
    MAX(CASE WHEN GROUP_POS = 3 THEN NUMEROSERIE END) AS SSCC3,
    MAX(CASE WHEN GROUP_POS = 4 THEN NUMEROSERIE END) AS SSCC4,
    MAX(CASE WHEN GROUP_POS = 5 THEN NUMEROSERIE END) AS SSCC5
FROM (
    SELECT 
        CODBULTO,
        NUMEROSERIE,
        -- 生成每个箱子下的序列号序号
        ROW_NUMBER() OVER (PARTITION BY CODBULTO ORDER BY NUMEROSERIE) AS GLOBAL_POS,
        -- 计算组号（每5个一组）
        FLOOR((ROW_NUMBER() OVER (PARTITION BY CODBULTO ORDER BY NUMEROSERIE) - 1) / 5) AS GROUP_ID,
        -- 计算组内的相对位置（1-5）
        MOD(ROW_NUMBER() OVER (PARTITION BY CODBULTO ORDER BY NUMEROSERIE) - 1, 5) + 1 AS GROUP_POS
    FROM XIPNMVSCHECK
) T
GROUP BY CODBULTO, GROUP_ID
ORDER BY CODBULTO, GROUP_ID;

说明：

• ROW_NUMBER()替代原查询中的子查询计数，效率更高且避免DISTINCT带来的排序问题
• GROUP_POS是每组内的位置（1到5），确保CASE判断能覆盖所有组的记录
• 按CODBULTO和GROUP_ID分组，聚合后得到每行5个序列号的结果

方案2：Oracle 11g及以上版本（使用PIVOT简化） #

Oracle 11g及以上支持PIVOT语法，代码更简洁：

SELECT 
    CODBULTO,
    SSCC1, SSCC2, SSCC3, SSCC4, SSCC5
FROM (
    SELECT 
        CODBULTO,
        NUMEROSERIE,
        'SSCC' || TO_CHAR(MOD(ROW_NUMBER() OVER (PARTITION BY CODBULTO ORDER BY NUMEROSERIE) - 1, 5) + 1) AS COL_NAME,
        FLOOR((ROW_NUMBER() OVER (PARTITION BY CODBULTO ORDER BY NUMEROSERIE) - 1) / 5) AS GROUP_ID
    FROM XIPNMVSCHECK
)
PIVOT (
    MAX(NUMEROSERIE)
    FOR COL_NAME IN ('SSCC1' AS SSCC1, 'SSCC2' AS SSCC2, 'SSCC3' AS SSCC3, 'SSCC4' AS SSCC4, 'SSCC5' AS SSCC5)
)
ORDER BY CODBULTO, GROUP_ID;

说明：

• 先生成列名标识（SSCC1到SSCC5）和组号，再通过PIVOT将行转列
• 语法更简洁，可读性更强

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