SQL Server 2017 STRING_AGG Order By

I’m using SQL Server 2017 Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) – 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: ).

I’m trying to sort the results of the STRING_AGG function.

This works fine

SELECT STRING_AGG(Category,', ') AS Result
FROM LegacyReviews_Categories

This gives me the error “Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘(‘.”

SELECT STRING_AGG(Category,', ') WITHIN GROUP ( ORDER BY Category ASC) AS Result
FROM LegacyReviews_Categories

Answer

I can repro this on SQL Server 2019 build 15.0.4198.2 too.

It just requires the query to be run in the context of a database set to COMPATIBILITY_LEVEL of 100 (2008).

All other (later) compatibility levels work.

The documentation states

STRING_AGG is available in any compatibility level.

And doesn’t mention this.

If you are unable to change the compatibility level of the database to something higher then possibly you will need to fall back to the old XML PATH method of concatenating ordered results.