Oracle Pivot如何将透视列别名改为QTY_PXX格式

现有如下数据及表结构：

drop table sample_data;
create table sample_data
(
    id_order        number  ,
    id_product      number  ,
    qty             number
);


insert into sample_data values (100,1,42);
insert into sample_data values (100,2,5);
insert into sample_data values (100,3,178);
insert into sample_data values (100,4,66);

需要将id_product字段值转为列，当前使用的Pivot语句如下：

select *
from 
(
    select id_order, id_product, qty from sample_data
)
pivot 
(
    sum(qty) as qty 
    for (id_product) 
    in 
    (
        1 as P01
        , 2 as P02
        , 3 as P03
        , 4 as P04
    )
);

注：原语句中id_product为数值类型，in子句里的取值无需加单引号，避免类型不匹配问题。

该语句生成的透视列别名为P01_QTY、P02_QTY等，但需求是将列别名改为QTY_P01、QTY_P02的格式，如何实现？

方式一：手动指定列别名（最直接） #

在外部SELECT子句中显式重命名每个透视列，覆盖Pivot生成的默认名称：

select 
    id_order,
    P01_QTY as QTY_P01,
    P02_QTY as QTY_P02,
    P03_QTY as QTY_P03,
    P04_QTY as QTY_P04
from 
(
    select id_order, id_product, qty from sample_data
)
pivot 
(
    sum(qty) as qty 
    for (id_product) 
    in 
    (
        1 as P01
        , 2 as P02
        , 3 as P03
        , 4 as P04
    )
);

方式二：调整Pivot内部命名逻辑 #

Oracle Pivot的列名生成规则是[IN子句中的别名]_[聚合函数后的AS后缀]。如果想直接生成QTY_P01格式的列名，可以去掉聚合后的as qty，直接把目标列名写在IN子句的AS中：

select *
from 
(
    select id_order, id_product, qty from sample_data
)
pivot 
(
    sum(qty) 
    for (id_product) 
    in 
    (
        1 as QTY_P01
        , 2 as QTY_P02
        , 3 as QTY_P03
        , 4 as QTY_P04
    )
);

这种方式下，聚合结果会直接使用IN子句中定义的别名作为列名，逻辑与原语句一致，无需额外重命名。

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