Background
Working on some Transact SQL code yesterday.
Ran into an error that was new to me.
Error
Error – Image
Error – Text
Msg 8711, Level 16, State 1, Line 80 Multiple ordered aggregate functions in the same scope have mutually incompatible orderings.
Sample Code
The original code is a bit complex and sharing it will distract from this post’s point.
So here we go with sample code:-
Outline
- Original
- The original code works
- We have a column areaCode
- The column also has a String_Agg
- The String_Agg orders by the areaCode column
- Code – Error
- The second code fails
- The failure is because we added a new column, areaCodeAlt
- The new column also has a String_Agg
- The String_Agg orders by the id column
- Code – Remediative
- The third code works
- It separates out each String-Agg/”Within Group” combination into its own common table expression ( cte )
Code
Code – Original
SQL
</pre> set nocount on go declare @tblAreaCode TABLE ( [id] int not null identity(1,1) , [stateOrRegion] varchar(100) not null , [city] varchar(100) not null , [areaCode] char(3) not null , [yearEffected] int null , [areaCodeAndyearEffected] as case when ( [yearEffected] is null) then [areaCode] else ( [areaCode] + ' (' + cast( [yearEffected] as varchar(10)) + ' )' ) end , [yearEffectedAndAreaCode] as case when ( [yearEffected] is null) then [areaCode] else ( + ' (' + cast( [yearEffected] as varchar(10)) + ' - ' + ' )' ) + ' ' + [areaCode] end ) /* https://en.wikipedia.org/wiki/List_of_area_code_overlays */ insert into @tblAreaCode ( [stateOrRegion] , [city] , [areaCode] , [yearEffected] ) values /* https://www.cbc.ca/news/canada/british-columbia/new-236-area-code-rolled-out-in-b-c-1.1358072#:~:text=Until%201996%2C%20604%20was%20the,across%20the%20province%20in%202008. */ ( 'British Columbia', 'British Columbia', '604', null ) , ( 'British Columbia', 'British Columbia', '250', 1996 ) , ( 'British Columbia', 'British Columbia', '778', 2001 ) /* https://en.wikipedia.org/wiki/Area_codes_678_and_470 */ , ( 'Atlanta', 'Georgia', '404', null ) , ( 'Atlanta', 'Georgia', '678', 1998 ) , ( 'Atlanta', 'Georgia', '470', 2010 ) select tblAC.[stateOrRegion] , tblAC.[city] , [areaCode] = STRING_AGG ( tblAC.[areaCode] , ', ' ) WITHIN GROUP ( ORDER BY tblAC.[areaCode] ASC ) from @tblAreaCode tblAC group by tblAC.[stateOrRegion] , tblAC.[city] order by tblAC.[stateOrRegion] , tblAC.[city] <pre>
Image
Code – Error
SQL
set nocount on go declare @tblAreaCode TABLE ( [id] int not null identity(1,1) , [stateOrRegion] varchar(100) not null , [city] varchar(100) not null , [areaCode] char(3) not null , [yearEffected] int null , [areaCodeAndyearEffected] as case when ( [yearEffected] is null) then [areaCode] else ( [areaCode] + ' (' + cast( [yearEffected] as varchar(10)) + ' )' ) end , [yearEffectedAndAreaCode] as case when ( [yearEffected] is null) then [areaCode] else ( + ' (' + cast( [yearEffected] as varchar(10)) + ' - ' + ' )' ) + ' ' + [areaCode] end ) /* https://en.wikipedia.org/wiki/List_of_area_code_overlays */ insert into @tblAreaCode ( [stateOrRegion] , [city] , [areaCode] , [yearEffected] ) values /* https://www.cbc.ca/news/canada/british-columbia/new-236-area-code-rolled-out-in-b-c-1.1358072#:~:text=Until%201996%2C%20604%20was%20the,across%20the%20province%20in%202008. */ ( 'British Columbia', 'British Columbia', '604', null ) , ( 'British Columbia', 'British Columbia', '250', 1996 ) , ( 'British Columbia', 'British Columbia', '778', 2001 ) /* https://en.wikipedia.org/wiki/Area_codes_678_and_470 */ , ( 'Atlanta', 'Georgia', '404', null ) , ( 'Atlanta', 'Georgia', '678', 1998 ) , ( 'Atlanta', 'Georgia', '470', 2010 ) /* Sample -02 */ select tblAC.[stateOrRegion] , tblAC.[city] , [areaCode] = STRING_AGG ( tblAC.[areaCode] , ', ' ) WITHIN GROUP ( ORDER BY tblAC.[areaCode] ASC ) , [areaCodeAlt] = STRING_AGG ( tblAC.[areaCode] , ', ' ) WITHIN GROUP ( ORDER BY tblAC.[id] ASC ) from @tblAreaCode tblAC group by tblAC.[stateOrRegion] , tblAC.[city]
Image
Code – Sample 03
SQL
set nocount on go declare @tblAreaCode TABLE ( [id] int not null identity(1,1) , [stateOrRegion] varchar(100) not null , [city] varchar(100) not null , [areaCode] char(3) not null , [yearEffected] int null , [areaCodeAndyearEffected] as case when ( [yearEffected] is null) then [areaCode] else ( [areaCode] + ' (' + cast( [yearEffected] as varchar(10)) + ' )' ) end , [yearEffectedAndAreaCode] as case when ( [yearEffected] is null) then [areaCode] else ( + ' (' + cast( [yearEffected] as varchar(10)) + ' - ' + ' )' ) + ' ' + [areaCode] end ) /* https://en.wikipedia.org/wiki/List_of_area_code_overlays */ insert into @tblAreaCode ( [stateOrRegion] , [city] , [areaCode] , [yearEffected] ) values /* https://www.cbc.ca/news/canada/british-columbia/new-236-area-code-rolled-out-in-b-c-1.1358072#:~:text=Until%201996%2C%20604%20was%20the,across%20the%20province%20in%202008. */ ( 'British Columbia', 'British Columbia', '604', null ) , ( 'British Columbia', 'British Columbia', '250', 1996 ) , ( 'British Columbia', 'British Columbia', '778', 2001 ) /* Sample -03 */ ; with cte01 as ( select tblAC.[stateOrRegion] , tblAC.[city] , [areaCodeAlt] = STRING_AGG ( tblAC.[areaCode] , ', ' ) WITHIN GROUP ( ORDER BY tblAC.[id] ASC ) , [areaCodeAltYear] = STRING_AGG ( tblAC.[areaCodeAndyearEffected] , ', ' ) WITHIN GROUP ( ORDER BY tblAC.[id] ASC ) from @tblAreaCode tblAC group by tblAC.[stateOrRegion] , tblAC.[city] ) select tblAC.[stateOrRegion] , tblAC.[city] , [areaCode] = STRING_AGG ( tblAC.[areaCode] , ', ' ) WITHIN GROUP ( ORDER BY tblAC.[areaCode] ASC ) , cte01.[areaCodeAlt] , cte01.[areaCodeAltYear] from @tblAreaCode tblAC inner join cte01 on tblAC.[stateOrRegion] = cte01.[stateOrRegion] and tblAC.[city] = cte01.[city] group by tblAC.[stateOrRegion] , tblAC.[city] , cte01.[areaCodeAlt] , cte01.[areaCodeAltYear]
Image
Source Code Control
Github
DanielAdeniji / TransactSQLStringAgg
Link
Summary
Transact SQL’s String Aggregation function, STRING_AGG, is very useful.
It has some quicks.
It is a first iteration effort and it will only get better.