How to Convert multiple rows into one with comma as separator - Oracle db

Question

I have an oracle table which has id and order_id columns. Table have same order_id with different id's.

How can I write a select for group same order_ids, and show in one line which seperated with comma;

Example;

      ORDER_ID                ID
623cdc7ff2f3603b06a283ff    8112686  
623cdc7ff2f3603b06a283ff    8116869
623cdc7ff2f3603b06a28400    8117671
623ce4068c47be1532c4c53c    8118392

Select result should be like that;

ORDER_ID                    ID
623cdc7ff2f3603b06a283ff    8112686 , 8116869
623cdc7ff2f3603b06a28400    8117671
623ce4068c47be1532c4c53c    8118392

d r · Accepted Answer · 2022-05-24 04:53:22Z

2

LISTAGG

Select ORDER_ID, LISTAGG(ID, ', ') WITHIN GROUP (ORDER BY ID)
From tbl
Group By ORDER_ID

answered May 24, 2022 at 4:53

d r

8,6722 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OldProgrammer · Accepted Answer · 2022-05-24 00:24:53Z

1

listagg function to the rescue:

select order_id, listagg(id,', ')
from test
group by order_id

answered May 24, 2022 at 0:24

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

1 Comment

user5683823 Over a year ago

Missing the mandatory within group (order by ...).

d r · Accepted Answer · 2022-05-24 04:42:46Z

0

Or, for older versions of Oracle you can use WM_CONCAT function. LISTAGG appeared at Oracle 11g Release 2.

WITH
    tbl AS
        (
            SELECT '623cdc7ff2f3603b06a283ff' "ORDER_ID", '8112686' "ID" FROM DUAL UNION ALL 
            SELECT '623cdc7ff2f3603b06a283ff' "ORDER_ID", '8116869' "ID" FROM DUAL UNION ALL
            SELECT '623cdc7ff2f3603b06a28400' "ORDER_ID", '8117671' "ID" FROM DUAL UNION ALL
            SELECT '623ce4068c47be1532c4c53c' "ORDER_ID", '8118392' "ID" FROM DUAL 
        )
--  ----------------------------------------------------------------------------------
Select ORDER_ID, WMSYS.WM_CONCAT(ID)
From tbl
Group By ORDER_ID

answered May 24, 2022 at 4:42

d r

8,6722 gold badges9 silver badges19 bronze badges

2 Comments

Littlefoot Over a year ago

If I were you, I wouldn't recommend anyone to use undocumented & unsupported features (such as WM_CONCAT)

d r Over a year ago

Thanks Littlefoot - here is a disclaimer, though the function works ok for me with older versions of Oracle. WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, make a User-Defined Aggregate Function.

Collectives™ on Stack Overflow

How to Convert multiple rows into one with comma as separator - Oracle db

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related