Really what you are looking to do is inline concatenation / aggregation. The two easiest methods are:

1. SQLCLR (works with SQL Server 2005 and newer, but not Azure SQL Database unless it is the new Managed Instance type)
1. The [STRING_AGG][1] aggregate function (works with SQL Server 2017 and newer, _and_ works on Azure SQL Database)

Currently, neither of these options works for you:

* Option \#1 is prohibited by policy, and (or due to) the fact that the client(s) will soon be migrating to Azure SQL Database which does not support SQLCLR (anymore; it did for about 18 months).
* Option \#2 is not possible due to being on SQL Server 2014 which does not have that function

This might be a long-shot, but given that you are only prohibited from using SQLCLR by policy, _and_ that they will be migrating to Azure SQL Datbase, you might could do the following:

1. Short-term (prior to migrating to Azure SQL Database): use a SQLCLR aggregate function
1. Long-term (upon migrating to Azure SQL Database): swap out the SQLCLR aggregate for `STRING_AGG`

While this does entail modifying queries upon migration, the modification is a simple replacement. Assuming that all stored procedures, functions, etc are stored in a repository (e.g. Git, SVN, etc), it should be fairly easy to do a mass replacement across multiple files. Then deploy, test, and commit to the repository.

For example (using the [SQL#][2] library that I wrote which has **Agg_Join** in the Free version):

    SELECT so.[schema_id],
           COUNT(*) AS [NumObjects],
           SQL#.Agg_Join(DISTINCT RTRIM(so.[type])) AS [Types]
    FROM   sys.objects so
    GROUP BY so.[schema_id];
    
    
    SELECT so.[schema_id],
           COUNT(*) AS [NumObjects],
           STRING_AGG(RTRIM(so.[type]), ',')
                  WITHIN GROUP (ORDER BY RTRIM(so.[type])) AS [Types]
    FROM   sys.objects so
    GROUP BY so.[schema_id];

These don't return identical results sets unless you remove the `DISTINCT` from `SQL#.Agg_Join(DISTINCT RTRIM(so.[type]))`. I kept that in there to show a case where it helps, and to point out that for some reason, `DISTINCT` is _not_ supported in the `STRING_AGG` function :(.

If you can do a Regular Expression (RegEx) replace, you can swap out:

`SQL#.Agg_Join({stuff})`

for:

`STRING_AGG({stuff}, ',') [WITHIN GROUP (ORDER BY {stuff})]`

with minimal effort. The `WITHIN GROUP` part is optional (I added it there since the `DISTINCT` in the SQLCLR version did the ordering). The main point being that the queries are otherwise untouched and should produce the same results, hence not needing a massive retesting effort.

In fact, if you create your own SQLCLR UDA such that it has the second parameter for the delimiter (the SQL# **Agg_Join** doesn't such that it can work in SQL Server 2005), you could name it "String_Agg". This would reduce the find/replace to be replacing `dbo.String_Agg(` with `String_Agg(`.

  [1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql
  [2]: https://SQLsharp.com/?ref=db_216953