aggregate function to concatenate strings in Verti

Question

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 

Answer 1

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.

Answer 2

SELECT id,
    MAX(DECODE(row_number, 1, a.name)) ||
    NVL(MAX(DECODE(row_number, 2, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 3, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 4, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 5, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 6, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 7, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 8, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 9, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 10, ',' || a.name)), '')||
    NVL(MAX(DECODE(row_number, 11, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 12, ',' || a.name)), '') ag
FROM
    (SELECT id, name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) row_number FROM test) a
GROUP BY a.id
ORDER BY a.id;

Answer 3

The other method is to use GROUP_CONCAT from the strings package on github.

select id, group_concat(name) over (partition by id order by name) ag
from mytable

However there are some limitations with this method since analytical udx won't allow you to include other aggregates (and you'll have to inline it or use with to add more data to it).