The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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