Equivalent of LISTAGG that returns an ARRAY rather than delimited string?

edited November 2022 in General Discussion

Does Vertica have a function similar to LISTAGG or GROUP_CONCAT that returns an ARRAY type?

The relatively new Vertica ARRAY type is awesome, but I think LISTAGG only returns a string.

Imaginary query below:

  SELECT manufact, ARRAY_CONCAT(type)
  FROM car_sales 
  GROUP BY manufact

EDIT I see it's currently possible to use LISTAGG to return a string and subsequently split that to an array using STRING_TO_ARRAY, but wonder if there's an efficiency cost to this

WITH a AS (
   SELECT manufact, LISTAGG(manufact) as types
   FROM public.car_sales
   GROUP BY 1
)
SELECT 
    *, 
   STRING_TO_ARRAY(types::!VARCHAR USING PARAMETERS collection_delimiter=',') FROM a ;

https://forum.vertica.com/discussion/241946/how-to-access-listagg-or-group-concat-in-vertica-9-3
https://forum.vertica.com/discussion/208697/does-vertica-have-an-equivalent-to-mysqls-group-concat-function

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file