Thanks for the question.

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

Last updated: August 04, 2023 - 5:38 am UTC

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

Rating

(28 ratings)

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

Comments

String Aggregation rocks!

Sean, October 10, 2012 - 11:52 am UTC

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

Sandeep Srivastava, December 14, 2012 - 10:31 am UTC

Very helful, thanks for this

humayun, March 14, 2013 - 6:36 am UTC

THANKS..VERY USEFUL

Translate list using other table

Rajesh, December 09, 2014 - 10:28 am UTC

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

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

Rajesh, December 23, 2014 - 7:31 am UTC

Thanks Tom!

helpful query for developers

nitin bhandari, January 05, 2015 - 6:10 am UTC

this really help me out from my issue

Listagg

Prasad Medepalli, April 25, 2015 - 6:33 am UTC

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

Keith Russell, May 22, 2016 - 4:02 pm UTC

Do not know what we in the Oracle Community would do without you. (struggle)

May 23, 2016 - 1:06 am UTC

Thanks for the feedback :-)

How to get this without ordering the concatenated column

Lakshman, January 11, 2017 - 3:28 pm UTC

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.

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

Satwinder, August 17, 2017 - 10:11 am UTC

That's exactly I was looking for. Thank you so much.

Great Response!!

Sangeeta Tulsiyan, February 13, 2019 - 9:55 am UTC

Thank you so much.

February 13, 2019 - 11:19 am UTC

You're welcome.

Extremely helpful and useful

Marge, October 29, 2019 - 6:47 am UTC

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.

October 29, 2019 - 11:18 am UTC

Glad we could help.

getting error as from keyword not found where expected

chandrakanth, February 05, 2020 - 7:31 am UTC

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.

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

chandrakanth, February 07, 2020 - 9:07 am UTC

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

Akshay, May 04, 2020 - 10:33 pm UTC

When we are using the function LISTAGG in Fusion SQL, it return with an error "ORA-00937: not a single-group group function"

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

dave, May 07, 2020 - 8:03 pm UTC

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?

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

Dave, May 13, 2020 - 5:02 pm UTC

Thanks worked perfectly

May 14, 2020 - 4:08 am UTC

glad we could help

padmaja, July 10, 2020 - 12:35 pm UTC

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

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;

Problem with ListAgg and Japanese or Madeiran char

joan, October 14, 2020 - 10:30 pm UTC

Using listagg on 11g DB using Japan data.
The data changed from this data in the rows:
JPN パーソナル休暇
JPN パーソナル休暇（午前休）
to this data using ListAgg:
JPN 0��0�0��f�; JPN 0��0�0��f�SHRMO�
Can you help me?

still good, still VERY usefull

VB, February 19, 2021 - 8:45 pm UTC

Thank you.

February 23, 2021 - 6:04 am UTC

glad we could help

Combine data of similar rows into 1 row

GG, June 17, 2021 - 11:00 am UTC

I have my table as below:
ID Ordered Returned
AI_1 100 null
AI_1 null 20

From this i want a single row as below:
ID Ordered Returned
AI_1 100 20

How can i achieve this? any help is appreciated.

June 22, 2021 - 1:40 am UTC

select id, max(ordered), max(returned)
from t
group by id

Satyen, July 30, 2021 - 3:04 pm UTC

listagg -
ORA-01489: result of string concatenation is too long

Any other alternate?

August 02, 2021 - 6:33 am UTC

https://github.com/connormcd/listagg_clob

Need to convert Listagg output to number

A reader, March 16, 2022 - 12:52 am UTC

select case when column=1 and row in (listagg(columns agregates like(1,2,3)) throws invalid number.

March 16, 2022 - 3:39 am UTC

"and NUMBER_COLUMN in (LISTAGG(....))"

might be transposed by the database as:

"and NUMBER_COLUMN in (to_nuimber(LISTAGG(....)))"

hence the issue. Try TO_CHAR around the NUMBER_COLUMN

Re: Translate list using other table

Stew Ashton, March 17, 2022 - 9:21 am UTC

Current versions of the Oracle database (patched 19c or later) have much, much better support for keeping an ordered list in one column: use a JSON array.

create table mytable (
  ci_name varchar2(100) primary key,
  old_support_groups varchar2(1000) not null
    check (old_support_groups is json),
  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');

create table mapping_table(
  old_support_group varchar2(100) primary key,
  new_support_group varchar2(100) not null unique
);

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');

commit;

With 21c, there could even be a multivalue function-based index on the JSON array. On the other hand, there is no way to guarantee that every value in the list exists in the mapping table.

I was curious to see what the code to translate the list would look like. If a value does not exist in the mapping table, it is left as is.

select ci_name,
  json_arrayagg(coalesce(new_support_group, old_support_group) order by grp_id) old_support_groups,
  any_value(column1) column1,
  any_value(column2) column2
from mytable
cross apply json_table(
  old_support_groups, '$[*]' columns
    grp_id for ordinality,
    old_support_group path '$'
)
left join mapping_table using(old_support_group)
group by ci_name;

CI_NAME     OLD_SUPPORT_GROUPS                                               COLUMN1    COLUMN2    
Server A    ["SupportGroup B","SupportGroup A","SG-C"]                       abc        xyz        
Server B    ["SG-C","SupportGroup X","SupportGroup one","SupportGroup P"]    pqr        lmn

March 17, 2022 - 1:53 pm UTC

Good suggestion

Edwin, March 30, 2022 - 7:02 am UTC

Hi,

I have this SQL below, which gives the result in the order that I expected which is good.

SQL01:

set linesize 200
set pagesize 10000
col role_id format a10
select db_unique_name ,
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') runtime ,
grantee ,
granted_role ,
( case granted_role
when 'A' then '101'
when 'B' then '102'
when 'C' then '103'
when 'D' then '104'
when 'E' then '105'
end ) role_id
from dba_role_privs, v$database
where granted_role in ( 'A', 'B', 'C', 'D' , 'E' )
order by grantee, role_id
;

Now, I modify it for use on a shell script using || ',' || so I can get a comma delimited list like below:

SQL02 in the shell script:

sqlplus -S "/as sysdba" << SQLPLUS | grep -v "^$" | awk 'NR > 2' | tee -a $OUTFILE
set linesize 200
set pagesize 50000
set echo off feedback off
set trimspool on
-- col role_id format a10
select db_unique_name || ',' || to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ',' ||
grantee || ',' ||
granted_role || ',' ||
( case granted_role
when 'A' then '101'
when 'B' then '102'
when 'C' then '103'
when 'D' then '104'
when 'E' then '105'
end ) role_id
from dba_role_privs, v\$database
where granted_role in ( 'A', 'B', 'C', 'D' , 'E' )
order by grantee, role_id
;
SQLPLUS

However the order is not as I will have expected, granted_role is sorted but the role_id is not sorted as in SQL01. I change SQL02 and remove the concatenation and use

set colsep ,

Now, it gives the same ordered list at SQL01.
However there are those wide gaps between the fields that I have to clean/trim up :(

Is the wrong sort order from using || ',' || or is it the CASE statement that is causing the wrong sort order?

Edwin, March 30, 2022 - 7:47 am UTC

I did the following to get it to sort like SQL01. Is this the only way?

order by grantee,
( case granted_role
when 'A' then '101'
when 'B' then '102'
when 'C' then '103'
when 'D' then '104'
when 'E' then '105'
end )
;

March 31, 2022 - 10:25 am UTC

If you alias a column expression, eg

select col1||col2||col3 col1
from ...
order by col1

then we interpret this as order by the *alias* not the original "col1"

So when you concatenated the columns, you are now ordering by the alias not the original role column.

A reader, July 25, 2023 - 8:04 am UTC

This works. But what do I use in the group by when I have some 20 columns in select statement as I am writing to develop a report?

August 04, 2023 - 5:38 am UTC

I think we need more info here. Maybe a full test case

A reader, March 13, 2025 - 3:59 pm UTC

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

String Aggregation rocks!

Concatenate Ouput Rows

Translate list using other table

Concatenate multiple results into one row

helpful query for developers

Listagg

Thank you very much

How to get this without ordering the concatenated column

Saved my day

Great Response!!

Extremely helpful and useful

getting error as from keyword not found where expected

getting error as from keyword not found where expected

The Function is not useful in Oracle Fusion based SQL

how do you combine every N rows into 1 row

thumbs up

Problem with ListAgg and Japanese or Madeiran char

still good, still VERY usefull

Combine data of similar rows into 1 row

Need to convert Listagg output to number

Re: Translate list using other table

More to Explore

SQL