3

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
evanburen
  • 267
  • 4
  • 16
  • Can you recreate your error in [SqlFiddle](http://sqlfiddle.com/#!18)? Your syntax appears correct to me. I tested on a dataset of mine by swapping out the table and column information. Perhaps it is data or data type related? – Josh Jay Feb 07 '22 at 19:54
  • Here is a working [SqlFiddle](http://sqlfiddle.com/#!18/8bfa8c/2) – Josh Jay Feb 07 '22 at 20:00
  • 3
    I can repro this on a database set to 2008 compatibility mode `100`. With `COMPATIBILITY_LEVEL` of `110` or later it works. – Martin Smith Feb 07 '22 at 20:04
  • 1
    @MartinSmith, interesting! I would suspect the first query wouldn't work either though – Josh Jay Feb 07 '22 at 20:05
  • The first query does work. It just throws the invalid syntax with `WITHIN GROUP` – Martin Smith Feb 07 '22 at 20:06
  • 1
    Doesn't seem like this limitation is documented https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15#remarks – Martin Smith Feb 07 '22 at 20:07
  • It must have something to do with the data itself. There are 50 category values so I'll try and figure out which one – evanburen Feb 07 '22 at 20:21
  • 1
    @user3641053 - no it has nothing to do with the data. It has to do with the compatibility level your database is set to. Have a look at `SELECT name, compatibility_level FROM sys.databases` – Martin Smith Feb 07 '22 at 20:22
  • No, you're right @MartinSmith. The compatibility level was set to 100 and after changing it works as expected. Thanks for the help. – evanburen Feb 07 '22 at 20:27
  • 1
    There might be reasons why it was on 2008 compat mode though. You should verify that changing it has no ill effects for you – Martin Smith Feb 07 '22 at 20:29

1 Answers1

8

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

enter image description here

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Interesting, is this a parse time error? If you use the query hint it succeeds (but I don't have a proper 2017 instance to test, and certainly not one on RTM+GDR path). https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d289785f12392185c2716e71a8dca13d – Aaron Bertrand Feb 07 '22 at 20:15
  • @AaronBertrand - yes. Parse time. And repros on latest 2019 build too – Martin Smith Feb 07 '22 at 21:03
  • 2
    I suspect this is because though `STRING_AGG` can be used in any compatibility, `WITHIN GROUP` cannot, as that was added in 2012 with `PERCENTILE_DISC` and `PERCENTILE_CONT`. Though I don't have a 2017 instance to test ideas on either. – Thom A Feb 07 '22 at 21:17
  • @Larnu quite likely. The issue reproduces on 2019 too so no need for 2017 – Martin Smith Feb 07 '22 at 21:21