Oracle SQL - Multiple rows into one field

I have a table like this:

enter image description here

I need a query (no PL/SQL) that shows this output:

enter image description here

So for each Product ID I want the distinct values of Delivery Type in the field Delivery Type.

Is there a way to get this result through a "simple" query in Oracle?

I am using Oracle 11g.

Thanks in advance !

1 answer

  • answered 2018-01-11 21:00 Vamsi Prabhala

    Use listagg after getting the distinct delivery types per product id. (Note that there is a 4000 character limit for the aggregated string.)

    select product_id,listagg(delivery_type,'/') within group (order by delivery_type)
    from (select distinct product_id,delivery_type from tbl) t
    group by product_id