Concat GROUP BY in Vertica SQL

Question

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 | greg |
| 02 | paul |
| 03 | greg |
| 04 | greg |
| 05 | paul |

SELECT name, {some concentration function} AS ids
FROM table
GROUP BY name

Returning

| name | ids        |
|======|============|
| greg | 01, 03, 04 |
| paul | 02, 05     |

I know MySQL has the CONCAT_GROUP function and I was hoping to solve this problem without installing more functions because of the environment. Maybe I can solve this problem using an OVER statement?

Vertica is a Postgres fork if I'm not mistaken. So maybe `string_agg` or `array_agg` is available there. — , Jan 10 '14 at 20:18

Kermit · Answer 1 · 2014-01-10T20:48:29.030

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

CREATE TABLE t1 (
  id int,
  name varchar(10)
);

INSERT INTO t1
SELECT 1 AS id, 'greg' AS name
UNION ALL
SELECT 2, 'paul'
UNION ALL
SELECT 3, 'greg'
UNION ALL
SELECT 4, 'greg'
UNION ALL
SELECT 5, '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(PARTITION BY name ORDER BY id) row_number FROM t1) a
GROUP BY a.name
ORDER BY a.name;

Result

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

This seems like a good real world solution. @Gregology, is there any chance that you need it to be an arbitrary length concatenation? — kimbo305, Jan 11 '14 at 03:06

score 4 · Answer 2 · edited Sep 28 '15 at 08:09

4

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;


in the Concatenate.cpp
replace : input_len*10
with : 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.

edited Sep 28 '15 at 08:09

Srini V

11,045
14
66
89

answered Oct 01 '14 at 12:21

ismail

368
4
6

3

Concatenate does not have the ability to use a separator, as stated in: https://community.dev.hp.com/t5/Vertica-Forum/Does-Vertica-have-an-equivalent-to-MySQL-s-GROUP-CONCAT-function/td-p/208697 as well as in Guillaume's answer, you must instead use https://github.com/vertica/Vertica-Extension-Packages/tree/master/strings_package which is a 3rd part UDF that is capable of doing this – user1084563 Jul 17 '15 at 18:46
1

@user1084563 This works fine, just do `rtrim(agg_group_concat(field || ', '),', ')`. The rtrim will remove the separator at the end and this method does not require analytical functions. – woot Jun 29 '16 at 18:44
@woot Interesting workaround, definitely didn't realize you could append within the function call like that. You may also want to add that to the vertica community thread i posted since that has not been proposed there. – user1084563 Jun 30 '16 at 21:54
@user1084563 [Ok](https://community.dev.hpe.com/t5/Vertica-Forum/Does-Vertica-have-an-equivalent-to-MySQL-s-GROUP-CONCAT-function/m-p/236799#M13157) – woot Jul 01 '16 at 00:00

score 2 · Accepted Answer · answered Jan 11 '14 at 18:11

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.

michaelxq · Answer 4 · 2023-09-01T10:59:57.933

0

A really old question, but as an update, you can use listagg() function

id	name
01	greg
02	paul
03	greg
04	greg
05	paul

SELECT name, listagg(id) AS ids FROM table GROUP BY name

That will return the desire output:

name	ids
greg	01, 03, 04
paul	02, 05

edited Sep 01 '23 at 10:59

answered Sep 01 '23 at 10:17

michaelxq

11
2

Concat GROUP BY in Vertica SQL

4 Answers4

Linked

Related