You can translate the question and the replies:

Column values concatenation

Hi Team, I'm looking for ways to concatenate two or more row values using the "|" separator. I found and used the GROUP_CONCATE() function, but it doesn't work for me using the "DISTINCT" clause. Here is an example of a working and non-working query: **Working (but with a different separator, a comma (',')) -** GROUP_CONCAT(DISTINCT ROS.route_of_manufacturing) AS "RouteofSynthesis" **Not functional -** GROUP_CONCAT('|', DISTINCT ROS.route_of_manufacturing) AS "RouteofSynthesis" What is the issue with the second query? Is there another method to do what is required?
user
28-07-2023 06:03:15 -0400
code

1 Answer

Hi, only GROUP_CONCAT with one parameter allows DISTINCT clause as shown [here](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/aggregation_functions/aggregation_functions#group-concat): ``` GROUP_CONCAT( [ DISTINCT | ALL ] <field name:identifier>) GROUP_CONCAT( [ <row separator:text> [, <field separator:text> ] ], <field name:identifier> [, <field name:identifier>]* ) ``` As an alternative you can use a subquery. For instance ``` select c1, GROUP_CONCAT('|', c1) from (select distinct c1, c2 from <your_view>) group by c2; ``` There are no other options. Hope this helps
Denodo Team
01-08-2023 03:32:17 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here