Simulate MySQL group_concat Function

[JimKnicely]

Hi,

In MySQL the group_concat function is used to returns a string result with the concatenated non-NULL values from a group.

Example from MySQL:

Code: Select all

(mydbadmin@localhost) [jim]> select * from combine;
+--------+
| name   |
+--------+
| raj    |
| sekhar |
| nicely |
+--------+
3 rows in set (0.00 sec)

(mydbadmin@localhost) [jim]> select group_concat(name) from combine;
+--------------------+
| group_concat(name) |
+--------------------+
| raj,sekhar,nicely  |
+--------------------+
1 row in set (0.00 sec)

Unfortunately, Vertica does not have the group_concat function natively.

One way to simulate this in Vertica is the following:

Code: Select all

dbadmin=> select * from combine;
  name
--------
 raj
 sekhar
 nicely
(3 rows)

dbadmin=> select MAX(DECODE(rn, 1, name)) ||
dbadmin->          NVL(MAX(DECODE(rn, 2, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 3, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 4, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 5, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 6, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 7, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 8, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 9, ',' || name)), '') ||
dbadmin->          NVL(MAX(DECODE(rn, 10, ',' || name)), '') group_concate
dbadmin->   from (select row_number() over() rn, name from combine) foo;
   group_concate
-------------------
 raj,sekhar,nicely
(1 row)

If there are more than 10 members, you'll have to modify the code to add more handlers. This is a HUGE limitation of course. It's hard to know in advance how group members you'll have.

Have fun!

[jpcavanaugh]

There is an extension (TRANSPOSE) in the Vertica github repository that might help you out -- https://github.com/vertica/Vertica-Exte ... _functions

[coalesce]

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 LIBRARY
CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
CREATE AGGREGATE FUNCTION agg_group_concat AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;

[JimKnicely]

Nice!

Funny though that the limitation of the function is about the same as the original post in this thread

From the /opt/vertica/sdk/examples/AggregateFunctions/Concatenate.cpp file:

/**
* User Defined Aggregate Function concatenate that takes in strings and concatenates
* them together. Right now, the max length of the resulting string is ten times the
* maximum length of the input string.
*/

[coalesce]

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

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.