Skip to Main Content
  • Questions
  • Concatenate multiple results into one row

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 08, 2012 - 3:55 pm UTC

Answered by: Connor McDonald - Last updated: July 11, 2020 - 6:01 am UTC

Category: SQL*Plus - Version: 11g

Viewed 100K+ times! This question is

You Asked

When I query a database that includes a particular field (Condition), it returns multiple rows of Conditions associated with the same result from another column (NCT_ID). See sample output below.

NCT_ID CONDITION
NCT00531284 Multiple Myeloma
NCT00531284 Lung Cancer
NCT00531284 Ovarian Cancer
NCT00531284 Renal Cancer

What I am looking for is a way to concatenate all the Condition fields which have the same NCT_ID into one row. So that the output will look like:

NCT_ID CONDITION
NCT00531284 Multiple Myeloma; Lung Cancer; Ovarian Cancer; Renal Cancer

Is this possible to do within the SQL script or is it better left to manipulate the output in Excel (which I don't know how to do either!)?

Thank you.



and we said...

listagg will get that for you. deptno = nct_id, ename = condition


ops$tkyte%ORA11GR2> select deptno, listagg(ename, ';' ) within group (order by ename) from scott.emp group by deptno order by deptno;

    DEPTNO
----------
LISTAGG(ENAME,';')WITHINGROUP(ORDERBYENAME)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        10
CLARK;KING;MILLER

        20
ADAMS;FORD;JONES;SCOTT;SMITH

        30
ALLEN;BLAKE;JAMES;MARTIN;TURNER;WARD


and you rated our response

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

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
Tom Kyte

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)
Chris Saxon

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.
Connor McDonald

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.
Chris Saxon

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.
Connor McDonald

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.
Chris Saxon

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.
Chris Saxon

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"
Chris Saxon

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?
Connor McDonald

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
Connor McDonald

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




Connor McDonald

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;


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.