Pivot : asign columns names [message #688105] |
Tue, 22 August 2023 02:51 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
Hi all,
I have this data :
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);
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
I want to alias the pivoted columns like this
ID_ORDER QTY_P01 QTY_P02 QTY_P03 QTY_P04
---------- ---------- ---------- ---------- ----------
100 42 5 178 66
Thanks in advance
Amine
|
|
|
Re: Pivot : asign columns names [message #688106 is a reply to message #688105] |
Tue, 22 August 2023 08:15 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Remove the "as qty" after "sum(qty)" on line 8 and add "qty_" to lines 12, 13, 14, and 15, as shown below.
SCOTT@orcl_12.1.0.2.0> select *
2 from
3 (
4 select id_order, id_product, qty from sample_data
5 )
6 pivot
7 (
8 sum(qty)
9 for (id_product)
10 in
11 (
12 '1' as QTY_P01
13 , '2' as QTY_P02
14 , '3' as QTY_P03
15 , '4' as QTY_P04
16 )
17 )
18 ;
ID_ORDER QTY_P01 QTY_P02 QTY_P03 QTY_P04
---------- ---------- ---------- ---------- ----------
100 42 5 178 66
1 row selected.
|
|
|