Home » SQL & PL/SQL » SQL & PL/SQL » listagg function in oracle (Oracle 11g R2, pl/sql)
icon5.gif  listagg function in oracle [message #662983] Thu, 18 May 2017 05:56 Go to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
select et.order_id,sum(et.qty * prd.prod_cost) as order_total,listagg(prod_alias, ',') within group (order by prod_alias) as ITEMS
from ext_ol et 
join product prd on et.palias=prd.prod_alias 
where et.order_id=order_id group by et.ORDER_ID;

The above query gives me the following output:

ID TOTAL ITEMS
1 395 MC
2 440 CTF
3 460 PT,VSK
4 215 CFR
5 875 CKMT,CMT,CT
6 440 CTF
7 630 CTL

I want to add the names and prices of each of the items in a particular order_id. How can I do that?
Re: listagg function in oracle [message #662991 is a reply to message #662983] Thu, 18 May 2017 06:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What should the new output look like?
Re: listagg function in oracle [message #662994 is a reply to message #662991] Thu, 18 May 2017 06:50 Go to previous messageGo to next message
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 #662997 is a reply to message #662994] Thu, 18 May 2017 07:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like you just need multiple listagg calls then
Re: listagg function in oracle [message #662999 is a reply to message #662997] Thu, 18 May 2017 07:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #663010 is a reply to message #663008] Thu, 18 May 2017 07:34 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Haha thanks I got it.
Re: listagg function in oracle [message #663030 is a reply to message #662983] Thu, 18 May 2017 13:58 Go to previous messageGo to next message
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.
Re: listagg function in oracle [message #663031 is a reply to message #663030] Thu, 18 May 2017 14:54 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Previous Topic: Use ":NEW" in Dynamic SQL in Trigger?
Next Topic: Percentage of occurrences of a row
Goto Forum:
  


Current Time: Fri Mar 29 02:40:15 CDT 2024