How to concatenate multiple rows in Oracle

How to concatenate multiple rows in Oracle

There are a few scenarios where we want to create a list of entries or list of entries within a group or list of unique entries or anything related to list. So, the question stands still, How to concatenate multiple rows in Oracle?
Answer to this is ListAgg. It concatenate multiple rows to a single string separating different values user defined delimiter. The output of listagg can be used in many real-time scenarios, for example, passing list of unique accounts to a subquery

Let’s dive in, we will be using a table called GROCERY which has different products, expiration status, product’s price, and expiry date.

Scenario 1: Simple list without any constraint.

SELECT LISTAGG(PRODUCT,’,’) WITHIN GROUP(ORDER BY PRODUCT) AS LISTOFPRODUCTS FROM GROCERY
How to concatenate multiple rows in Oracle

Scenario 2: List with only distinct products.

SELECT LISTAGG(PRODUCT,’,’) WITHIN GROUP(ORDER BY PRODUCT) AS LISTOFPRODUCTS FROM (SELECT DISTINCT PRODUCT FROM GROCERY)

Scenario 3: List on the basis of some other group.

SELECT IS_EXPIRED,LISTAGG(PRODUCT,’,’) WITHIN GROUP(ORDER BY PRODUCT)  AS LISTOFPRODUCTS FROM GROCERY GROUP BY IS_EXPIRED
How to concatenate multiple rows in Oracle within group

We can change its group and see a different output.

SELECT RATE,LISTAGG(PRODUCT,’,’) WITHIN GROUP(ORDER BY PRODUCT) AS LISTOFPRODUCTS FROM GROCERY GROUP BY RATE
How to concatenate multiple rows within group
Link to oracle posts

Leave a Reply

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close