Background

Working on a script, but noticed an error when I tried to sort the data using the String_Agg within group clause.

Code

Let us reproduce using a sample data set.

SQL

Sample -01

SQL

set nocount on
go

set XACT_ABORT on
go

--select @@version as [@@version]
--go

declare @tbl TABLE
(
      [id] int not null
        identity(1,1)

    , [state] varchar(100) not null

    , [city]  varchar(100) not null

)

insert into @tbl ([state], [city])

select 'Texas', 'San Antonio'

union all

select 'Texas', 'Houston'

union all

select 'Florida', 'Miami'

union all

select 'Florida', 'Tampa Bay'

union all

select 'Connecticut', 'Hartford'

union all

select 'Connecticut', 'New Haven'

union all

select 'Connecticut', 'Bridgeport'

union all

select 'Connecticut', 'Bristol'

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

Output

Sample -02

Outline

If one reviews our output, one will notice that the cities are listed based on the order in which they were added.

They are not listed alphabetically.

Let us try to address that by using the group by within clause.

SQL

set nocount on
go

set XACT_ABORT on
go

--select @@version as [@@version]
--go

declare @tbl TABLE
(
      [id] int not null
        identity(1,1)

    , [state] varchar(100) not null

    , [city]  varchar(100) not null

)

insert into @tbl ([state], [city])

select 'Texas', 'San Antonio'

union all

select 'Texas', 'Houston'

union all

select 'Florida', 'Miami'

union all

select 'Florida', 'Tampa Bay'

union all

select 'Connecticut', 'Hartford'

union all

select 'Connecticut', 'New Haven'

union all

select 'Connecticut', 'Bridgeport'

union all

select 'Connecticut', 'Bristol'

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

            within group
            (
                order by
                    tbl.[city]
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

Output

Output – Text

Msg 102, Level 15, State 1, Line 83
Incorrect syntax near 'within'.

Output – Image

Troubleshooting

SQL Server Version

Failing Instance

Let us issue select @@version against our instance.

select @@version

Output

Output – Text

Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Output – Image

Remediation

Apply SQL Server Cumulative Patch.

SQL Server Version

Good Instance

Let us issue select @@version against another instance where the code works :-

Output

Output – Text

Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Output – Image

Validation

Code

 

SQL

set nocount on
go

set XACT_ABORT on
go

--select @@version as [@@version]
--go

declare @tbl TABLE
(
      [id] int not null
        identity(1,1)

    , [state] varchar(100) not null

    , [city]  varchar(100) not null

)

insert into @tbl ([state], [city])

select 'Texas', 'San Antonio'

union all

select 'Texas', 'Houston'

union all

select 'Florida', 'Miami'

union all

select 'Florida', 'Tampa Bay'

union all

select 'Connecticut', 'Hartford'

union all

select 'Connecticut', 'New Haven'

union all

select 'Connecticut', 'Bridgeport'

union all

select 'Connecticut', 'Bristol'

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

            within group
            (
                order by
                    tbl.[city]
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

Output

Explanation

Our cities are alphabetically ordered with each state.

Source Code Control

GitHub

TransactSQL.String_Agg/error/incorrectSyntaxNearWithin/state.city/
Link

 

Version

Please keep in mind that String_Agg is supported on SQL Server v2017 and beyond.

The version that it is failing on is :-

1. @@VERSION
   • Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) – 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
     • Cummulative Patch :- RTM-CU9-GDR
     • Version Number :- 14.0.3035.2
     • Build Date :- Jul 6 2018 18:24:36

Version Verifiable Works :-

1. @@VERSION
   • Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) – 14.0.3048.4 (X64) Nov 30 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
     • Cummulative Patch :- RTM-CU13
     • Version Number :- 14.0.3048.4
     • Build Date :- Nov 30 2018 12:57:58

 

Summary

Not for everyone.

But, for those that it applies to :-

1. Good Engineering
   • Trust, but verify
   • Patch your systems