Return to Revisions

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)
2. The STRING_AGG 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
2. 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# 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.