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.
