CODEWITHSUNDEEP
sqloracle-databaseaggregate
user1250264
user1250264

Reputation: 905

oracle LISTAGG grouping multiple row results into one row not working. ORA-00909

I saw a post here to use LISTAGG to group multiple rows into one row under 1 column, I looked at the post example and oracle website but I can't get the query to work.

in sql developer, the error is ORA-00909: invalid number of arguments

I have the following query

select d.id, d.name, d.date_sale, d.address, d.city, d.state, d.zipcode, d.description, d.explanation, d.received_date, 
       listagg(dd.my_id, dd.customer_name, dd.category, dd.transaction_date, ';' 
       ) within group (order by dd.transaction_date)
from table1 d
left join table2 dd on d.id = dd.my_id
where d.id =1 and d.isActive =1

any help is appreciated.

Upvotes: 0

Views: 225

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Concatenate the values together and add a group by:

select d.id, d.name, d.date_sale, d.address, 
       listagg(dd.my_id || dd.customer_name || dd.category, dd.transaction_date, ';' 
              ) within group (order by dd.transaction_date)
from table1 d left join
     table2 dd
     on d.id = dd.my_id
where d.id =1 and d.isActive = 1
group by d.id, d.name, d.date_sale, d.address;

Upvotes: 1

Related Questions