使用PIVOT转换行数据遇问题：现有代码无法得到预期输出

我手头有一份库存交易历史数据（原格式见截图），想通过PIVOT把它转成指定格式（目标格式见截图），但写的SQL跑不出预期结果，代码如下：

Select part_no, part_description, 1 x1 , 2 as x2, 3 as x3
From (
Select part_no, part_description, quantity --, cost
, rn

From (
select part_no, ifsapp.Inventory_Part_API.Get_Description(CONTRACT,PART_NO) as Part_description
, TRUNC(date_applied, 'DAY')+1 start_of_week, Sum( quantity ) quantity
, Sum( ifsapp.Inventory_Transaction_Cost_API.Get_Sum_Unit_Cost(transaction_id, 'TRUE', 'TRUE') *QUANTITY ) as Cost
, rn

from ifsapp.INVENTORY_TRANSACTION_HIST Left Outer Join Rowsnum On TRUNC(date_applied, 'DAY')+1 = dt

where part_no='CUTC12444' and 
direction='-' 
and date_applied between 
    Case When '&No_Of_Weeks' Is Null Then trunc(add_months(sysdate, -11),'mm')
         When '&No_Of_Weeks' Is not Null Then To_Date('&Period_ending_date','DD/MM/YYYY') - ('&No_Of_Weeks'*7) End
   and
    Nvl(To_Date('&Period_ending_date','DD/MM/YYYY'), last_day(trunc(sysdate)) )
 
and contract Not In ('CPH','CTM','VMS','CGP')
Group by part_no, ifsapp.Inventory_Part_API.Get_Description(CONTRACT,PART_NO), TRUNC(date_applied, 'DAY')+1, rn

) data Where Nvl(rn,0) != 0
) src
Pivot
( Sum( Quantity ) for rn In ( 1,2,3 ) ) 

问题排查与修正要点： #

• PIVOT列名逻辑错误：主查询里硬写1 x1 , 2 as x2, 3 as x3会直接输出固定数字，而非PIVOT聚合后的结果。正确做法是在PIVOT的IN子句中给列定义别名，比如1 AS x1, 2 AS x2, 3 AS x3，再在主查询直接引用这些别名。
• 分组维度冗余：内层查询已按周和rn分组求和，额外嵌套的筛选层可能导致数据丢失。可简化层级，确保PIVOT能捕获所有需聚合的行。
• 关联空值处理：Rowsnum表的关联可能产生空rn值，虽已加Nvl(rn,0) != 0，但要确认Rowsnum的dt是否与TRUNC(date_applied, 'DAY')+1完全匹配，避免遗漏有效数据。

修正后的示例代码：

Select part_no, part_description, x1, x2, x3
From (
    select part_no, 
           ifsapp.Inventory_Part_API.Get_Description(CONTRACT,PART_NO) as Part_description,
           quantity,
           rn
    from (
        select part_no, 
               CONTRACT,
               Sum( quantity ) quantity,
               rn
        from ifsapp.INVENTORY_TRANSACTION_HIST 
        Left Outer Join Rowsnum On TRUNC(date_applied, 'DAY')+1 = dt
        where part_no='CUTC12444' 
          and direction='-' 
          and date_applied between 
              Case When '&No_Of_Weeks' Is Null Then trunc(add_months(sysdate, -11),'mm')
                   When '&No_Of_Weeks' Is not Null Then To_Date('&Period_ending_date','DD/MM/YYYY') - ('&No_Of_Weeks'*7) End
              and Nvl(To_Date('&Period_ending_date','DD/MM/YYYY'), last_day(trunc(sysdate)) )
          and contract Not In ('CPH','CTM','VMS','CGP')
        Group by part_no, CONTRACT, rn
    ) data 
    Where rn is not null
) src
Pivot
( Sum( Quantity ) for rn In ( 1 AS x1, 2 AS x2, 3 AS x3 ) ) 

注意：

• 把Get_Description调用移到外层，避免分组时重复计算（原分组包含该函数会增加不必要的分组维度）
• PIVOT的IN子句直接定义别名，主查询引用这些别名获取聚合结果
• 内层分组仅保留必要维度，防止拆分正确的聚合值

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