I am looking to collect all records with errors for each CorrelationId. In Kusto that looks like this:
logs_CL
| summarize
Errors=make_list_if(Error, isnotempty(Error)),
by CorrelationId
When looking at AzureSql aggregate functions it seems none of those make_list/make_set families of commands are available https://learn.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver16
Is there any way to aggregate the contents of many records into one record? That is, we need to retain information from each record, not just compute a statistic applying to the entire set of records (such as min max stdev mean).
>Solution :
If you’re on SQL Server version 2017+, you can use STRING_AGG aggregate function (see docs at https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16) to concatenate info into a single column.
A very basic example:
select top 30 so.name AS object, STRING_AGG(sc.name, ',') WITHIN GROUP (ORDER BY sc.column_id) AS columns
from sys.objects so
inner join sys.columns sc
ON sc.object_id = so.object_id
GROUP BY so.name
This outputs some tables and their columns separated by ‘,’