CODEWITHSUNDEEP
sqloracle-databaselistagg
anonymous
anonymous

Reputation: 503

Using LISTAGG function in Oracle

I have two tables

category
--------

id      product     seq_num
cat12   prod12      0
cat23   prod12      1
cat34   prod12      2

prod
-----

sku     prod    seq_num
sku123  prod12  0
sku234  prod12  1
sku345  prod12  2

I am trying to use LISTAGG function and bring output like:

output
------
skuid   prod    catids
sku123  prod12  cat12,cat23,cat34
sku234  prod12  cat12,cat23,cat34
sku345  prod12  cat12,cat23,cat34

When i use category table alone i am able to use LISTAGG and get the output aggregated properly(without skuid)

using below query:

SELECT product,LISTAGG(id, ',') 
WITHIN GROUP (ORDER BY id) AS catids
FROM category
GROUP BY product;

but i am not able to join this with prod table and get the desired output. Please help.

Upvotes: 3

Views: 5499

Answers (2)

ecemturkay
ecemturkay

Reputation: 1

You can write the query as follows:

SELECT p.sku "skuid", c.product "prod",
       LISTAGG(c.id, ',') WITHIN GROUP (ORDER BY id) 
         OVER (PARTITION BY c.product) AS catids
  FROM category c
  INNER JOIN prod p 
    ON c.product = p.prod
  ORDER BY "skuid", c.product;

Also you can check this; it is more complicated version of usage together of listagg and join functions

Upvotes: 0

sagi
sagi

Reputation: 40481

You can do it after the join:

SELECT s.sku,t.product,
       LISTAGG(t.id, ',') WITHIN GROUP (ORDER BY id) AS catids
FROM category t
INNER JOIN prod s
 ON(t.product = s.prod)
GROUP BY t.product,s.sku;

Upvotes: 3

Related Questions