I’ve had CROSS APPLY on the mind recently. You could probably tell since its been the subject of my last two posts. Since it’s been on my mind a lot I started to wonder if I could use it to solve some problems I’ve run across in the past. In this particular case running an aggregate, say a max, over multiple columns in a row.
For example let’s say I have a table as follows.
CREATE TABLE QuarterlyAverages ( [Year] smallint, [Q1Average] money, [Q2Average] money, [Q3Average] money, [Q4Average] money)
Yes I realize it’s denormalized, but first it’s an example and second I’ve seen tables similar to this in the real world.
Given that table, I am tasked to produce the highest quarterly average per year. Previously I would have written something complicated and annoying using lots of cases. Using CROSS APPLY however I can do this.
SELECT [Year], MAX(Quarters.Average) FROM QuarterlyAverages CROSS APPLY (VALUES (1, [Q1Average]), (2, [Q2Average]), (3, [Q3Average]), (4, [Q4Average]) ) Quarters (Q, Average) GROUP BY [Year]
I have to admit I love it when I find a smooth solution for problems I’ve struggled with in the past!
Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, CROSS APPLY, language sql, microsoft sql server, sql statements, T-SQL