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.

Related Posts

Trailing Spaces and String Comparisons

Bert Wagner shows how SQL Server handles trailing spaces when comparing two strings: The LEN() function shows the number of characters in our string, while the DATALENGTH() function shows us the number of bytes used by that string. In this case, DATALENGTH is equal to 10. This result is due to the padded spaces occurring […]

Read More

T-SQL Bugs with Joins

Itzik Ben-Gan takes us through four bugs or oddities around joins: The order counts are now correct, but the total freight values are not. Can you spot the new bug? The new bug is more elusive because it manifests itself only when the same customer has at least one case where multiple orders happen to […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031