It will take more than a union unfortunately. You would need something like:
select max(ID), name, string_agg(origin, ',') from (select ... union ...) group by name
Thanks Ramesh and Steven for helping me with this solution.
Steven told you that I applied the sentence you suggested and I was even investigating it. However, the teiid does not recognize the String_agg function.
I was thinking that maybe this is because of the way I designed the preview before executing the sentence you recommended.
Here are the steps of my development:
- First I create a source view for each client table
2. Then I created a stored procedure so that each view indicated the source where it came from.
3.Afterwards, I placed this stored procedure in a view.
4.Once I got a view of each client stating their source, I applied a select union to have a single client view.
5.But as the discussion began in the blog, clients that have two source sources are duplicated in the view, for this I applied the function String_agg and as a result I got this:
You do not really need the procedure layer and then back to view layer, you could have done in the first view layer. For example:
CF_clientView: select 'CompoFe' as flunte, a.dni as dni, ... FROM clientCF as a;
FS_clientView: select 'FeSalaud' as flunte, a.dni as dni, ... FROM clientFS as a;
Then you are at the last step before the union view. The string_agg function looks right. What the JDV version you are running?