sqlaggregatevertica

aggregate function to concatenate strings in Vertica


have a table in vertica: test like this:

ID     |   name
1      |    AA
2      |    AB
2      |    AC
3      |    AD
3      |    AE
3      |    AF

how could I use an aggregate function or how to write a query to get data like this (vertica syntax)?

ID    |  ag
1     |  AA
2     |  AB, AC
3     |  AD, AE, AF 

Solution

  • First, you'll need to compile the udx for agg_concatenate.

    -- Shell commands
    cd /opt/vertica/sdk/examples/AggregateFunctions/
    g++ -D HAVE_LONG_INT_64 -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value -fPIC -o Concatenate.so Concatenate.cpp /opt/vertica/sdk/include/Vertica.cpp
    
    -- vsql commands
    CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
    CREATE AGGREGATE FUNCTION agg_concatenate AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;
    

    Then you can do a query like:

    select id, rtrim(agg_concatenate(name || ', '),', ') ag
    from mytable
    group by 1
    order by 1
    

    Uses rtrim to get rid of the last ', '.

    If you need the aggregate to be sorted a certain way, you may need to select/sort in an inline view or with first.