TIL: Another Great Use Of APPLY

Yesterday, at DevTeach, I got to see Itzik Ben-Gan give a session on advanced T-SQL operations, mostly around gaps & islands but also including a couple other gems.  Something that caught my eye was an interesting way of using APPLY to unpivot data.  I know a few good uses of the APPLY operator and this was a new one on me.

First, the setup.  Suppose we have a table which has pivoted quantity and value metrics for a number of years, and we’re bringing it into SQL Server as-is.

DROP TABLE IF EXISTS #Sales;
CREATE TABLE #Sales
(
	Product VARCHAR(50),
	Qty2013 INT,
	Qty2014 INT,
	Qty2015 INT,
	Val2013 INT,
	Val2014 INT,
	Val2015 INT
);

INSERT INTO #Sales(Product, Qty2013, Qty2014, Qty2015, Val2013, Val2014, Val2015) VALUES
('P1', 200, 230, 255, 1995, 2448, 3006),
('P2', 126, 129, 127, 448, 463, 451),
('P3', 600, 16000, 38880, 750, 24000, 60000),
('P4', 390, 380, 370, 3000, 2900, 2800),
('P5', 125, 125, 125, 17008, 17008, 17008);

Performing any kind of aggregation on this is painful, so we’d like to unpivot the data. The method that I’m most familiar with is SUM(CASE) or MAX(CASE), but in this case, because we’re dealing with unique rows, we can skip the aggregation:

SELECT
	s.Product,
	y.[Year],
	CASE
		WHEN y.Year = 2013 THEN Qty2013
		WHEN y.Year = 2014 THEN Qty2014
		WHEN y.Year = 2015 THEN Qty2015
	END AS Quantity,
	CASE
		WHEN y.Year = 2013 THEN Val2013
		WHEN y.Year = 2014 THEN Val2014
		WHEN y.Year = 2015 THEN Val2015
	END AS [Value]
FROM #Sales s
	CROSS JOIN (VALUES(2013),(2014),(2015)) y([Year]);

This code works, but if you have dozens of years, it gets messy writing those case statements and you’re a bit more likely to make a mistake when refactoring code. Here’s a simpler version using CROSS APPLY:

SELECT
	s.Product,
	y.[Year],
	y.Quantity,
	y.[Value]
FROM #Sales s
	CROSS APPLY (VALUES
		(2013, [Qty2013], [Val2013]),
		(2014, [Qty2014], [Val2014]),
		(2015, [Qty2015], [Val2015])
	) y([Year], Quantity, [Value]);

It’s a little easier to read than the other version, and adding additional years is pretty straightforward.  That makes for a great tip when you’re trying to refactor poorly-thought-out tables or bring into your system potentially well-thought-out flat files.