Search code examples
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.