Menu
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
379
To concatenate two strings on a single line, use the concatenation operator (two consecutive vertical bars).
The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. It does not add the separator at the end of the result string. In this syntax: input_string is any type that can be converted VARCHAR and NVARCHAR when concatenation.
Concatenation is the process of combining two or more strings to form a new string by subsequently appending the next string to the end of the previous strings. In Java, two strings can be concatenated by using the + or += operator, or through the concat() method, defined in the java. lang. String class.
Concatenation is the process of appending one string to the end of another string. You concatenate strings by using the + operator. For string literals and string constants, concatenation occurs at compile time; no run-time concatenation occurs. For string variables, concatenation occurs only at run time.
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.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
