Concatenate non-NULL Values from a Group

Jim_Knicely

Vertica 9.1.1-4 introduces an extremely useful aggregate function named LIST_AGG which returns a string with concatenated non-NULL values from a group.

Example:

dbadmin=> SELECT * FROM test ORDER BY group_id;
group_id |  name
----------+---------
        1 | ANDRIUS
        1 | DAVE
        1 | JIM
        1 | KRISTEN
        2 | BRYAN
        2 | SCOTT
        3 | CURTIS
        3 | LENOY
        3 | ROBERT
        4 | CARLOS
        4 |
(11 rows)

dbadmin=> SELECT LISTAGG(name) FROM test;
                             LISTAGG
-----------------------------------------------------------------
ANDRIUS,DAVE,JIM,KRISTEN,BRYAN,SCOTT,CURTIS,LENOY,ROBERT,CARLOS
(1 row)

dbadmin=> SELECT group_id, LISTAGG(name) FROM test GROUP BY group_id ORDER BY group_id;
group_id |         LISTAGG
----------+--------------------------
        1 | ANDRIUS,DAVE,JIM,KRISTEN
        2 | BRYAN,SCOTT
        3 | CURTIS,LENOY,ROBERT
        4 | CARLOS
(4 rows)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm

Have fun!

Jim_Knicely

Here's another example showing how to change the separator which defaults to a comma:

dbadmin=> SELECT group_id,
dbadmin->        LISTAGG(name USING PARAMETERS separator='|')
dbadmin->   FROM test
dbadmin->  GROUP BY group_id
dbadmin->  ORDER BY group_id;
 group_id |         LISTAGG
----------+--------------------------
        1 | ANDRIUS|DAVE|JIM|KRISTEN
        2 | BRYAN|SCOTT
        3 | CURTIS|LENOY|ROBERT
        4 | CARLOS
(4 rows)