Unpivoting With APPLY

I have a post on using the APPLY operator to unpivot data:

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:

1 2 3 4 5 6 7 8 9 10 11

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.

APPLY is an elegant solution to so many different classes of problem.