Sorting with string_agg

Transact-SQL
1

Hi all

I am trying to order within a string_agg command. I've read posts here and the suggestions have been to use "order by" or "within group". I've tried both without any luck.

I am using MS SERVER 2017.

This is my little test:

create table catTest
(
 str1 varchar(15),
);

insert into catTest (str1)
values ('Sydney'), ('Melb'), ('Bris')

I want to get: "Bris, Melb, Sydney"

These are the 2 sql commands I have run - each with errors:

    select  STRING_AGG(str1, ', ' order by str1) 
    from catTest

Error: Msg 156, Level 15, State 1, Line 38. Incorrect syntax near the keyword 'order'.

select STRING_AGG(str1, ', ') WITHIN GROUP (order by str1)
from catTest
Error: Msg 102, Level 15, State 1, Line 39 Incorrect syntax near '('.

Any assistance would be greatly appreciated

Thanks Paul

2

I don't have SQL 2017, but this worked in Azure

create table #catTest
(
str1 varchar(15),
);

insert into #catTest (str1)
values ('Sydney'), ('Melb'), ('Bris')

select STRING_AGG(str1, ',' ) within group (order by str1)
from #catTest

3

Ok thanks.

My DB was set to 2008 compatibility. The WITHIN GROUP (ORDER BY) only works with 2012 and up compatibility.

Thanks for taking the time to respond