Concatenate non-NULL Values from a Group

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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)

Have fun!

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited November 6

    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)
    
Sign In or Register to comment.