Closest equivalent to kusto's make_list_if() / make_set_if() in Azure SQL Server

Question

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).

Answer 1

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 ','