Calculating a difference between two columns within an UNPIVOT

Question

I am trying to perform an UNPIVOT, but one of my columns was actually the difference between two columns, and SSMS is not accepting that as one of the values in the creation of the un-pivoted field.

Example:

UNPIVOT(                                                                                                 
        Points
        for PointType in (                                                                                   
                          HomeTeamPoints
                        , (HomeTeamPoints - TotalPoints)
                        , TotalPoints

        ) as pnts

This works perfectly if I comment out the difference field, but once that's in it tells me "incorrect syntax near '('". Once i remove the parentheses it says "Incorrect Syntax near '-'"

Anyone know if there's a way to make this go through?

Thanks a lot

Answer 1

Don't use unpivot. Use apply:

select v.*
from t cross apply
     (values ('HomeTeamPoints', HomeTeamPoints),
             ('Diff', HomeTeamPoints - TotalPoints),
             ('TotalPoints', TotalPoints)
     ) v(which, points);

unpivot is bespoke syntax that does only one thing. apply implements lateral joins. These are very powerful types of joins and are part of the standard. Unpivoting is only the beginning of what you can do with apply.