String Aggregation rocks!
October 10, 2012 - 11:52 am UTC
Reviewer: Sean from Madison, Wisconsin, USA
Tom,
May the Swedish Oracle SQL Bikini Team bus break down in front of your home! Your expertise, graciousness, and quick response are greatly appreciated. This solves a problem that has vexed me for days. Many thanks!
Concatenate Ouput Rows
December 14, 2012 - 10:31 am UTC
Reviewer: Sandeep Srivastava from UK
Very helful, thanks for this
March 14, 2013 - 6:36 am UTC
Reviewer: humayun
THANKS..VERY USEFUL
Translate list using other table
December 09, 2014 - 10:28 am UTC
Reviewer: Rajesh from Bangalore, India
CREATE TABLE mytable (
CI_name VARCHAR2(100),
old_support_groups VARCHAR2(1000),
column1 VARCHAR2(100),
column2 VARCHAR2(100)
);
INSERT INTO mytable VALUES ('Server A','SG-B;SG-A;SG-C', 'abc', 'xyz');
INSERT INTO mytable VALUES ('Server B','SG-C;SG-X;SG-1;SG-P', 'pqr', 'lmn');
--We have MAPPING TABLE IN which we have mpping OF OLD support GROUP TO NEW support GROUP
CREATE TABLE Mapping_table
( Old_support_group VARCHAR2(100)
, new_support_group VARCHAR2(100)
);
INSERT INTO Mapping_table VALUES ('SG-A' , 'SupportGroup A');
INSERT INTO Mapping_table VALUES ('SG-B' , 'SupportGroup B');
INSERT INTO Mapping_table VALUES ('SG-C' , 'SupportGroup C');
INSERT INTO Mapping_table VALUES ('SG-D' , 'SupportGroup D');
INSERT INTO Mapping_table VALUES ('SG-1' , 'SupportGroup one');
INSERT INTO Mapping_table VALUES ('SG-X' , 'SupportGroup X');
INSERT INTO Mapping_table VALUES ('SG-P' , 'SupportGroup P');
-- using the mapping table would like to genrate the translated support groups list in same order and it should be seperated BY ";"
-- below query translate the group names but order of the group in list is changed which we dont want
SELECT CI_Name, Old_support_groups
,(SELECT listagg(Mapping_table.NEW_SUPPORT_GROUP,';') WITHIN GROUP ( ORDER BY NULL)
FROM Mapping_table
WHERE (';'||mytable.OLD_SUPPORT_GROUPS||';' LIKE '%;' ||Mapping_table.OLD_SUPPORT_GROUP||';%')) new_support_groups
, column1, column2
FROM mytable;
CI_NAME OLD_SUPPORT_GROUPS NEW_SUPPORT_GROUPS COLUMN1 COLUMN2
Server A SG-B;SG-A;SG-C SupportGroup A;SupportGroup B;SupportGroup C abc xyz
Server B SG-C;SG-X;SG-1;SG-P SupportGroup C;SupportGroup P;SupportGroup X;SupportGroup one pqr lmn
Expected output IS
CI_NAME OLD_SUPPORT_GROUPS NEW_SUPPORT_GROUPS COLUMN1 COLUMN2
Server A SG-B;SG-A;SG-C SupportGroup B;SupportGroup A;SupportGroup C abc xyz
Server B SG-C;SG-X;SG-1;SG-P SupportGroup C;SupportGroup X;SupportGroup one;SupportGroup p pqr lmn
Whats the best way of archive the above.
Regards,
Rajesh
Followup
December 09, 2014 - 6:21 pm UTC
I'd start by redesigning my schema to not include a comma delimited list of attributes in my tables. I'd be using a relational schema design. Then this will be easy.
I'm serious - who does this?
sigh, it is so sad that since this mistake is made so often, I happen to be able to work with it with my eyes closed. I'm not saying this will be efficient - but it will work.
we have to turn your silly delimited list into proper rows
so we can join (you know, what we do in relational databases)
so I can aggregate the string again to give you back your bad idea....
ops$tkyte%ORA11GR2> with data
2 as
3 (
4 select ci_name, trim(';' from sg ) sg, to_number(substr(column_value,1,3),'000') rn, substr(column_value,4) sg_fixed,
5 column1, column2
6 from (
7 select mytable.*, column_value
8 from (select ci_name, ';'||old_support_groups||';' sg, column1, column2 from mytable) mytable,
9 table( cast( multiset(
10 select
11 to_char(level,'fm000')||
12 trim( substr (sg,
13 instr (sg, ';', 1, level ) + 1,
14 instr (sg, ';', 1, level+1) - instr (sg, ';', 1, level) -1 ) )
15 as token
16 from dual
17 connect by level <= length(sg)-length(replace(sg,';',''))-1
18 ) as sys.odciVarchar2List ) )
19 )
20 ),
21 data2
22 as
23 (
24 select *
25 from data t1, mapping_table t2
26 where t1.sg_fixed = t2.old_support_group
27 )
28 select ci_name, sg, listagg( new_support_group, ';' ) within group (order by rn) new_sg, max(column1), max(column2)
29 from data2
30 group by ci_name, sg
31 order by ci_name
32 /
CI_NAME SG NEW_SG MAX(COLUMN MAX(COLUMN
---------- ------------------------------ -------------------------------------------------- ---------- ----------
Server A SG-B;SG-A;SG-C SupportGroup B;SupportGroup A;SupportGroup C abc xyz
Server B SG-C;SG-X;SG-1;SG-P SupportGroup C;SupportGroup X;SupportGroup one;Sup pqr lmn
portGroup P
Concatenate multiple results into one row
December 23, 2014 - 7:31 am UTC
Reviewer: Rajesh from Bangalore, India
Thanks Tom!
helpful query for developers
January 05, 2015 - 6:10 am UTC
Reviewer: nitin bhandari from india
this really help me out from my issue
Listagg
April 25, 2015 - 6:33 am UTC
Reviewer: Prasad Medepalli from El dorado hills Ca USA
its very useful. Only , is there a way I can eliminate duplicates that will be even more useful. For instance I did something like listagg(address_state) it came back with ca;ca;ca;ca;az;tx; i wish I could make it give ca;az;tx.
Thank you very much
May 22, 2016 - 4:02 pm UTC
Reviewer: Keith Russell from California USA
Do not know what we in the Oracle Community would do without you. (struggle)
Followup
May 23, 2016 - 1:06 am UTC
Thanks for the feedback :-)
How to get this without ordering the concatenated column
January 11, 2017 - 3:28 pm UTC
Reviewer: Lakshman from India
Hi,
This is working fine. But I am having another requirement.
I want to get this concatenated value as per the order of the columns present in the table, not by ORDER BY clause.
For example, I have rows below.
ABC
XYZ
DEF
I want output like below only.
ABC,XYZ,DEF
Please suggest.
Followup
January 12, 2017 - 2:09 am UTC
A list such as
ABC
XYZ
DEF
*does* have an ordering of some sort - there is always a *reason* that ABC came out before XYZ. So you will possibly be ordering by the primary key. If you think there is *no* ordering, then the order is effectively random, ie, the order in which the rows were simply obtained from disk/memory. You could hence do LISTAGG ... ( order by null ) or (order by rowid) but that doesnt make a lot of sense, because the results could change at any moment (eg if a row was relocated)
Saved my day
August 17, 2017 - 10:11 am UTC
Reviewer: Satwinder from India
That's exactly I was looking for. Thank you so much.
Great Response!!
February 13, 2019 - 9:55 am UTC
Reviewer: Sangeeta Tulsiyan from London, United Kingdom
Thank you so much.
Followup
February 13, 2019 - 11:19 am UTC
You're welcome.
Extremely helpful and useful
October 29, 2019 - 6:47 am UTC
Reviewer: Marge from Elyria, OH
Thanks, much, Tom! The LISTAGG command is extemely helpful! I have never heard of it before this, but it is doing exactly what I was hoping for, although my next task will be to attempt two items from each line for a report that requires a single line for each entity.
Followup
October 29, 2019 - 11:18 am UTC
Glad we could help.
getting error as from keyword not found where expected
February 05, 2020 - 7:31 am UTC
Reviewer: chandrakanth from India
Hi,
i have a similar requirement where i used below code and getting error.
select un, listagg(text, ';') within group (order by text) from (select pbh.trading_partner_id ||' : '||a.requirement_id un,
(select display_name from per_person_names_f where person_id = a.person_id and name_type = 'GLOBAL'and sysdate between effective_start_date and effective_end_date)
|| ' : '|| a.internal_note text
from pon_team_requirement_scores a,pon_bid_headers pbh,pon_auction_headers_all paha
where pbh.auction_header_id = a.auction_header_id
and pbh.bid_number = a.bid_number
and a.auction_header_id = paha.auction_header_id
and paha.document_number = 'ESLSE/COR/NEG/PRF/2019/00079'
and a.internal_note is not null)
group by un
order by un
when i run the above query am getting error as from key word not found where expected.could you please help.
Followup
February 05, 2020 - 9:07 am UTC
Which version of Oracle Database are you using? Listagg was added in 11.2
getting error as from keyword not found where expected
February 07, 2020 - 9:07 am UTC
Reviewer: chandrakanth from India
Hi,
i have a similar requirement where i used below code and getting error.
select un, listagg(text, ';') within group (order by text) from (select pbh.trading_partner_id ||' : '||a.requirement_id un,
(select display_name from per_person_names_f where person_id = a.person_id and name_type = 'GLOBAL'and sysdate between effective_start_date and effective_end_date)
|| ' : '|| a.internal_note text
from pon_team_requirement_scores a,pon_bid_headers pbh,pon_auction_headers_all paha
where pbh.auction_header_id = a.auction_header_id
and pbh.bid_number = a.bid_number
and a.auction_header_id = paha.auction_header_id
and paha.document_number = 'ESLSE/COR/NEG/PRF/2019/00079'
and a.internal_note is not null)
group by un
order by un
when i run the above query am getting error as from key word not found where expected.could you please help.
am using this for fusion. not sure where is the issue in above query... but am able to achieve my reuqirment with different way by using the above listtag logic.
thanks for the support.
Followup
February 07, 2020 - 9:36 am UTC
Have you checked the database version? I can't see any obvious issues in this query.
The Function is not useful in Oracle Fusion based SQL
May 04, 2020 - 10:33 pm UTC
Reviewer: Akshay from India
When we are using the function LISTAGG in Fusion SQL, it return with an error "ORA-00937: not a single-group group function"
Followup
May 05, 2020 - 3:07 pm UTC
Have you GROUPed BY any SELECTed columns not in LISTAGG?
how do you combine every N rows into 1 row
May 07, 2020 - 8:03 pm UTC
Reviewer: dave from Austin, TX
Hi, to expand on this approach a liitle. in stead of listagg() combining all the rows into a single row, how do you combine every N rows into a single row?
Say my data set has 100 rows, how do I combine every 5 rows into 1 row & resulting in 20 rows, instead of 100 rows into 1 row?
Followup
May 11, 2020 - 5:13 am UTC
You just something to group by, so for example:
SQL> with emp_plus_seq as
2 ( select e.*, row_number() over ( order by empno ) as seq
3 from emp e
4 )
5 select trunc((seq-1)/5), listagg(ename,',') within group ( order by empno ) str
6 from emp_plus_seq
7 group by trunc((seq-1)/5);
TRUNC((SEQ-1)/5) STR
---------------- -----------------------------------------------------------------------
0 SMITH,ALLEN,WARD,JONES,MARTIN
1 BLAKE,CLARK,SCOTT,KING,TURNER
2 ADAMS,JAMES,FORD,MILLER
thumbs up
May 13, 2020 - 5:02 pm UTC
Reviewer: Dave
Thanks worked perfectly
Followup
May 14, 2020 - 4:08 am UTC
glad we could help
July 10, 2020 - 12:35 pm UTC
Reviewer: padmaja from Mumbai,India
My questioon here is if we have data like below:
NCT_ID CONDITION
NCT00531284 Multiple Myeloma
NCT00531284 Lung Cancer
NCT00531284 Ovarian Cancer
NCT00531284 Renal Cancer
is it possible to group and get single value of column
Expected output:
NCT00531284 Multiple Myeloma
Lung Cancer
Ovarian Cancer
Renal Cancer
Followup
July 11, 2020 - 6:01 am UTC
select
case when row_number() over ( partition by nct_id order by condition ) = 1 then nct_id end bct,
condition
from my_table;