This is part six in a series on window functions in SQL Server.
One of a Kind: The Ordered Set Function
As of SQL Server 2019, there is only one ordered set function:
STRING_AGG(). I like
STRING_AGG() a lot, especially because it means my days of needing to explain the
FOR XML PATH trick to concatenate values together in SQL Server are numbered.
STRING_AGG() is interesting in that we categorize it as a window function and yet it violates my first rule of window functions: there isn’t an
OVER() clause. Instead, it accepts but does not require a
WITHIN GROUP() clause. Let’s see it in action.
The Wide World Importers database has a table,
Application.StateProvinces, which includes the state/province code and the sales territory controlling that state or province. It looks a bit like so:
Suppose we want to see which states (and provinces) are in each sales territory, and we want a comma-delimited list of these states (…and provinces). That’s easy: I’ll just use my
STUFF() FOR XML PATH trick.
There’s a better way.
The Better Way
Let’s get sales territory and a list of states separated by commas.
SELECT SalesTerritory, STRING_AGG(StateProvinceCode, ',') AS StatesList FROM Application.StateProvinces s GROUP BY SalesTerritory;
This is great, but I’m a stickler for lexicographic ordering and want my states ordered accordingly. Please make that happen or I will open a sev 1 trouble ticket and will call about it every 20 minutes until you complete the task.
Bringing Order to the Universe
To create an ordering within each sales territory, let’s add a
WITHIN GROUP() clause, wherein we can turn this into an ordered set function. That there’s the name of it, Maude!
SELECT SalesTerritory, STRING_AGG(StateProvinceCode, ',') WITHIN GROUP(ORDER BY StateProvinceCode) AS StatesList FROM Application.StateProvinces s GROUP BY SalesTerritory;
WITHIN GROUP() clause, as we learned last time around, operates within each group, not each row. You might have noticed the
GROUP BY clauses here, which I’ve generally been avoiding for window functions. That’s because the
WITHIN GROUP() clause does not accept a
PARTITION BY statement; it allows only an ordering statement, but that’s good enough for this case.
One of a Kind?
Here’s where I yank the rug out from under you and proclaim that, in fact, we have several ordered set functions in SQL Server: the statistical distribution functions! The way to tell an ordered set function from any other sort of function is that ordered sets use
WITHIN GROUP(), sort of like how window functions use
OVER(). You may recall from the prior post that statistical window functions like
PERCENTILE_DISC() can also accept a
WITHIN GROUP() clause, making them ordered set functions as well. The difference between those four and
STRING_AGG() is that the statistical functions require an
OVER() clause whereas
STRING_AGG() does not want one.