Function string_agg() postgresql data base

1

Hi guys !!!

Is there any alternative to using the “string_agg” function from postgresql database in dremio?

Example below:

SELECT string_agg(PARECER_CONSULTOR, ’ - ')
FROM PESQUISA.PLANO_TRABALHO
WHERE ID_CONSULTOR = 7054310

2

Dremio Functions : https://docs.dremio.com/sql-reference/sql-functions/

Dremio has a bunch of String and Aggregation functions, does not have anything that will implement Postgress string_agg equivalent out of the box : https://www.postgresqltutorial.com/postgresql-aggregate-functions/postgresql-string_agg-function/

3

Is there any way that a loop can create over query so multiple records can be set separated with comma?

4

@munish.singla

Currently Dremio does not support equivalent of an Oracle PL/SQL. Direct ANSI SQL is what is supported

Thanks
Bali

5

Thanks for reply @balaji.ramaswamy
Is there any way I can create a loop to generate that kind of required resultset?

Thanks
Munish

6

@munish.singla

As I had mentioned, Dremio does not support loops inside the SQL construct

Thanks

7

Found a way to do this. Not sure when this functionality was released, with which version of dremio, but now you can use LIST_AGG Dremio, tested a couple of examples and it works like a charm.