Concatenate non-NULL Values from a Group: Quick Tip

Posted November 6, 2018 by Jim Knicely, Vertica Principal Solution Architect

Designers discussing project in office sample library
Vertica 9.1.1-4 introduces an extremely useful aggregate function named LISTAGG, 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) Have fun!