Merging a union to a single query

Question

I have the following query to produce the Id's and scores of certain products

Select
  tm.product_id,
  listagg(tm.book_id || '(' || tm.score || ')',',')
    within group (order by tm.product_id) as matches
from
  tl_product_match tm 
where
  tm.book_id is not null 
group by
  tm.product_id
union 
Select
  stm.product_id,
  listagg(stm.video_id || '(' || stm.score || ')',',')
    within group (order by stm.product_id) as matches
from
  tl_product_match stm
where
  stm.video_id is not null
group by
  stm.product_id

The query produces the output as shown below:

productId | matches
---------------------------------------------
1         | 123(30), 76565, 7687(500), 243(5)
2         | 352(30), 9(5), 34234(500), 43(5)
2         | 25(30), 78, 324(500), 23434(5)
3         | 546(30), 768, 34234(500), 324(5)

Two questions:

• Is it possible to modify the query to remove the union and still produce the same result?

• ProductId 2 is repeated twice (i.e. one row for each of the unions), what can i do to show productId 2 in the same row? i.e.

  productId |  matches
  -----------------------------------------------------------------------------
  1         |  123(30), 76565, 7687(500), 243(5)
  2         |  352(30), 9(5), 34234(500), 43(5), 25(30), 78, 324(500), 23434(5)
  3         |  546(30), 768, 34234(500), 324(5) 
  

Thanks in advance.

Answer 1

In the case when book_id and video_id have same type you can use this:

SELECT
   product_id ,listagg(gr_id || '(' || score || ')',',') within group (order by product_id) as matches
FROM
(
    SELECT
       product_id, score, book_id gr_id
    FROM tl_product_match
    WHERE book_id is not null 
    UNION ALL
    SELECT
       product_id, score, video_id gr_id
    FROM tl_product_match
    WHERE video_id is not null
) A
GROUP BY product_id

I believe that each line contains information either about the book or the video. This might work.

SELECT
    product_id,
    listagg(COALESCE(book_id, video_id) || '(' || score || ')',',') within group (order by product_id) as matches
FROM tl_product_match
GROUP BY product_id