Denodo column DISTINCT values concatenation

Question

I'm looking for ways to concatenate two or more row values using the "|" separator. I found and used the GROUP_CONCAT() 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?

Answer 1

It's my understanding you're asking about using the DISTINCT clause with the GROUP_CONCAT function in Denodo. The linked post in the Denodo Community, found here, addresses your question.

The only way to utilize the DISTINCT clause with the GROUP_CONCAT function is by specifying one parameter

GROUP_CONCAT( [ DISTINCT | ALL ] <field name:identifier>)

Additionally, a subquery can be used to achieve the desired outcome:

select c1, GROUP_CONCAT('|', c1) 
from (select distinct c1, c2 from <your_view>)
group by c2;

Unfortunately, no other alternatives are available.

Hope this helps.