Oracle Pivot：轴柱别名

sigwle7e 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(130)

我有这个数据：

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值旋转到列中

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_ORDER    P01_QTY    P02_QTY    P03_QTY    P04_QTY
---------- ---------- ---------- ---------- ----------
       100         42          5        178         66

现在，我想像这样给透视列取别名

ID_ORDER    QTY_P01    QTY_P02    QTY_P03    QTY_P04
---------- ---------- ---------- ---------- ----------
       100         42          5        178         66

提前感谢Amine

oracle

来源：https://stackoverflow.com/questions/76951735/oracle-pivot-pivoted-colums-alias

1条答案

按热度按时间

t2a7ltrp1#

不要使用SELECT *，而是命名您的列并提供新的别名。

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的IN子句中定义别名（而不是也用于聚合）：

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
    )
);

对于样本数据，两者都输出：
| ID_订单|QTY_P01| QTY_P02| QTY_P03| QTY_P04|
| --|--|--|--|--|
| 100 | 42 | 5 | 178 | 66 |
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

Oracle Pivot：轴柱别名

1条答案

相关问题

热门标签

最新问答