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