Listagg in SQLSERVER

Make the impossible possible: Using STUFF + FOR XML PATH

-- "Because STRING_AGG doesn't go with all SQL Server versions"
SELECT 
    YourGroupingColumn,
    STUFF(
        (
            SELECT ', ' + YourColumn
            FROM YourTable
            WHERE (YourCondition)
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
    ) AS ResultList
FROM YourTable
GROUP BY YourGroupingColumn;


For those of you on SQL Server versions 2016 or earlier, the STUFF and FOR XML PATH methods come to your rescue, pulling off some STRING_AGG-style magic!

Taking it up a level: Uniqueness and order

Show distinct values only, maestro!

-- "Well, it's certainly a 'distinct' pleasure to meet you, STRING_AGG!"
SELECT STRING_AGG(DISTINCT YourColumn, ';') 
FROM YourTable;


Are you tired of seeing duplicate values in your melody? Just use the DISTINCT keyword to filter out the noise.

The orchestra follows the conductor

-- "STRING_AGG awaits the conductor's instructions - 'And... Order by, NOW!'"
SELECT STRING_AGG(YourColumn, ',' ORDER BY YourOrderColumn) 
FROM YourTable
GROUP BY YourGroupingColumn;


The ORDER BY clause in STRING_AGG functions like a conductor, ensuring a well-coordinated performance.

Fine-tuning your performance: Considerations and gotchas

Performance impact: Your strings, your scale

String functions can have performance impacts on large datasets. So, compile your melody with care.

XML entitization woes

Got strange symbols like & in your result? Patch it up with type conversion when using FOR XML PATH to avoid these XML-special characters.

Harmony with your SQL version

Choosing the right instrument

-- "What year is it? The year of db's birth matters!"


Knowing your database version lets you choose the right function.

SQLite's unique symphony

-- "SQLite's mob, the 'GROUP_CONCAT' gang unites to deliver harmonious outputs."
SELECT GROUP_CONCAT(YourColumn, ',') 
FROM YourTable
GROUP BY YourGroupingColumn;


Sqlite offers similar functionality with GROUP_CONCAT.

Perfecting the performance

Omit leading commas with panache

-- "Who likes unnecessary commas? Not us!"
STUFF(...).value('.', 'NVARCHAR(MAX)'), 1, Len(',')+1, ''


Use STUFF to cut off leading commas. No more awkward punctuation at the start of your result set.

Understanding your score at the end

With listed results displaying combined values for each unique field, understanding your concatenated data becomes a breeze.

Official docs: The composer's manual

The official documentation offers the most authentic instructions for playing your SQL symphonies.