Concat Group By In Vertica Sql

I need to get a comma separated list of ids as a field for a messy third party api :s This is a simplified version of what I am trying to achieve. | id | name | |====|======| | 01

Solution 1:

You'll have to use OVER() with NVL() (you'll have to extend the concatenation for more than 10 instances per name):

CREATETABLE t1 (
  id int,
  name varchar(10)
);

INSERTINTO t1
SELECT1AS id, 'greg'AS name
UNIONALLSELECT2, 'paul'UNIONALLSELECT3, 'greg'UNIONALLSELECT4, 'greg'UNIONALLSELECT5, 'paul';

COMMIT;

SELECT name,
    MAX(DECODE(row_number, 1, a.id)) ||
    NVL(MAX(DECODE(row_number, 2, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 3, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 4, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 5, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 6, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 7, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 8, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 9, ','|| a.id)), '') ||
    NVL(MAX(DECODE(row_number, 10, ','|| a.id)), '') id
FROM
    (SELECT name, id, ROW_NUMBER() OVER(PARTITIONBY name ORDERBY id) row_number FROM t1) a
GROUPBY a.name
ORDERBY a.name;

Result

 name |  id
------+-------
 greg | 1,3,4
 paul | 2,5

Solution 2:

Have look at Concatenate UDAF in vertica examples which comes with vertica installation that's the mysql equivalent. you can just directly install it.

more /opt/vertica/sdk/examples/AggregateFunctions/Concatenate.cpp

-- Shell comppile
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

-- Create LIBRARYCREATE LIBRARY AggregateFunctionsConcatenate AS'/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
CREATE AGGREGATE FUNCTION agg_group_concat ASLANGUAGE'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;


in the Concatenate.cpp
replace : input_len*10with : 65000

there is two place you have to replace this value in the code.

65000 is the max length you can get with varchar. and since vertica doesnt uses all of 65000 for the values smaller than 65000 character you are fine.

Solution 3:

The easiest on the long term is to use one of the official Vertica UDFs to be found on github at https://github.com/vertica/Vertica-Extension-Packages/tree/master/strings_package which provides a group_concat function. The installation procedure is to found in the README, and examples are even provided.