CODEWITHSUNDEEP
sqloracle-database
Саят Оразов
Саят Оразов

Reputation: 55

How can I concatenate values in one cell based on a condition in Oracle SQL?

I have a table with values:

ID Type Text
1 note note by user1
2 remark remark by user
3 note note by user2
4 note note by user 3
5 remark remark by adm

How can I create another column where I could select concatenated values from 'Text' column. I need to select only Type column and concatenate values with a delimeter based on the type value. Table should look like this:

Type Text
note note by user1; note by user2; note by user3
remark remark by user; remark by adm

Thank you in advance!

Upvotes: 0

Views: 92

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

One option would be to use listagg

with x ( id, category, comments )as 
(
select 1 , 'note'   , 'note by user1'   from dual union all
select 2 , 'remark' , 'remark by user'  from dual union all
select 3 , 'note'   , 'note by user2'   from dual union all
select 4 , 'note'   , 'note by user 3'  from dual union all
select 5 , 'remark' , 'remark by adm'   from dual 
)
select category, listagg(comments,';' ) within group (order by id)
from x 
group by category ;

db<>fiddle here

Upvotes: 2

Related Questions