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 24 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.