Hi! I need to create a new column by concatenating a string field from a related table.
For example, let's suppose I have a table "Foo", with the primary column "Name" and an "N:1" relationship with "Contact". What I would like to do is something like this:
Contact
| FullName | Rollup-like Column from Foo |
| John | A, B, C |
Foo
| Name | FullName (Contact) |
| A | John |
| B | John |
| C | John |
In SQL, it would be something like this:
select [C].[ContactId], string_agg([F].[Name], N', ') as [Rollup-like Column from Foo]
from [Contact] as [C]
left join [Foo] as [F]
on [C].[ContactId] = [F].[ContactId]
group by [C].[ContactId];
How can I achieve this? Although it will be used in a model-driven app, I strongly prefer a backend solution, so my logic stays the same, no matter how I consume the data.
Kind regards,
Davide
