I have come across this at work several times.

I found a solution using a custom C# CLR Aggregate function. Interestingly enough, the example MSDN uses for aggregated functions is concatenate:

https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregate-invoking-functions

Its...