Home » SQL & PL/SQL » SQL & PL/SQL » listagg function in oracle (Oracle 11g R2, pl/sql)
|
|
Re: listagg function in oracle [message #662994 is a reply to message #662991] |
Thu, 18 May 2017 06:50 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
Well, I am not sure how the new output will look like but here is what I have in mind, if its possible:
ID-----TOTAL---ITEMS-----------------PRICES-----------------NAME
1------ 395---- MC------------------- 250-------------------*list of Item names like 'ITEMS' and "PRICES"*
2------ 440---- CTF------------------ 300
3------ 460---- PT,VSK--------------- 150,180
4------ 215---- CFR------------------ 195
5------ 875---- CKMT,CMT,CT---------- 200,190,255
6------ 440---- CTF------------------ 300
7------ 630---- CTL------------------ 250
Not sure whether it can be done. If not like this then any other way? Basically I want the total, the item_name, prices of the items in the order like above.
[Updated on: Thu, 18 May 2017 06:53] Report message to a moderator
|
|
|
|
Re: listagg function in oracle [message #662999 is a reply to message #662997] |
Thu, 18 May 2017 07:05 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
I have no clue how to do it. Since I need the prices and names of only those items in a particular order_id.
I can get the total in different view, and the order details in a different view but this is what I want to achieve.
is there any way I can do it without the listagg? that will also do.
[Updated on: Thu, 18 May 2017 07:05] Report message to a moderator
|
|
|
Re: listagg function in oracle [message #663008 is a reply to message #662999] |
Thu, 18 May 2017 07:31 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Think for a minute.
you're using listagg to concatenate the prod_alias to give the item list for each order_id
The name list will be a concatenation of a different column from product. It'll need to be grouped and order in the exact same way as the item list.
Can you really not guess how to do that with a second listagg call?
After that price should be easy.
|
|
|
|
Re: listagg function in oracle [message #663030 is a reply to message #662983] |
Thu, 18 May 2017 13:58 |
|
robfeist
Messages: 2 Registered: May 2017 Location: Pennsylvania
|
Junior Member |
|
|
I made up some quantities and prices for your data. Would this result format be ok?
create table product (prod_alias varchar2(5), prod_name varchar2(20), prod_cost number);
insert into product values ('MC','MC desc', 395);
insert into product values ('CTF','CTF desc', 220);
insert into product values ('PT','PT desc', 100);
insert into product values ('VSK','VSK desc', 260);
insert into product values ('CFR','CFR desc', 215);
insert into product values ('CKMT','CKMT desc', 75);
insert into product values ('CMT','CMT desc', 250);
insert into product values ('CT','CT desc', 250);
insert into product values ('CTL','CTL desc', 630);
create table sales_order (order_id number, qty number, prod_alias varchar2(5));
insert into sales_order values (1,1,'MC');
insert into sales_order values (2,2,'CTF');
insert into sales_order values (3,2,'PT');
insert into sales_order values (3,1,'VSK');
insert into sales_order values (4,1,'CFR');
insert into sales_order values (5,5,'CKMT');
insert into sales_order values (5,1,'CMT');
insert into sales_order values (5,1,'CT');
insert into sales_order values (6,2,'CTF');
insert into sales_order values (7,1,'CTL');
select et.order_id,sum(et.qty * prd.prod_cost) as order_total
,listagg('[QTY '||et.qty||' '||prd.prod_name||' '||prd.prod_cost||']', ',') within group (order by prd.prod_alias) as ITEMS
from sales_order et join product prd on et.prod_alias=prd.prod_alias
group by et.ORDER_ID
order by et.order_id;
ORDER_ID ORDER_TOTAL ITEMS
1 395 [QTY 1 MC desc 395]
2 440 [QTY 2 CTF desc 220]
3 460 [QTY 2 PT desc 100],[QTY 1 VSK desc 260]
4 215 [QTY 1 CFR desc 215]
5 875 [QTY 5 CKMT desc 75],[QTY 1 CMT desc 250],[QTY 1 CT desc 250]
6 440 [QTY 2 CTF desc 220]
7 630 [QTY 1 CTL desc 630]
7 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:54:06 CDT 2024
|