Active Query Builder support area

LISTAGG

Avatar
  • updated
  • Completed

Does Active Query Builder provide the ability to parse LISTAGG functions?

Avatar
Andrey Zavyalov, PM

Hello,

You asked:

Does Active Query Builder provide the ability to parse LISTAGG functions?

Active Query Builder has support for LISTAGG with Oracle SQL syntax, but it doesn't have support for it with DB2.

With what SQL syntax you need to use this function?

Avatar
Kelly
WITH c1 AS(SELECT1 AS ID, 'Bananas' PNAME
FROM dual),
c2 AS(SELECT2 AS ID,
'Apples' PNAME
FROM dual),
c3 AS(SELECT2 AS ID,
'Peaches' PNAME
FROM dual)
SELECT ListAgg(d3.ID ||': '|| d3.PNAME,', ') WITHIN GROUP(ORDER BY
d3.ID, d3.PNAME)AS LIST1
FROM(SELECT c1.ID,
c1.PNAME
FROM c1
UNION ALL
SELECT*
FROM c2
UNION ALL
SELECT*
FROM c3) d3

Avatar
Kelly

I see LISTAGG is supported in our newest version of Active Query Builder

Avatar
Kelly

1: Bananas, 2: Apples, 2: Peaches is returned

Avatar
Kelly

I am trying to write a query where I would get a column for the ID and the list of PNAME grouped on ID.

ID NAME_LIST
1 Bananas
2 Apples, Peaches

Any ideas?