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 :-
- @@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
- 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)
Version Verifiable Works :-
- @@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
- 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)
Summary
Not for everyone.
But, for those that it applies to :-
- Good Engineering
- Trust, but verify
- Patch your systems