Building queries using pivot - Don't forget the alias

[Peter Schmitz]

TLDR: When writing queries using the PIVOT function, the alias at the end of the pivot.

Today, while working with the Pivot function, it took me a short while to figure out why my query was not working.

My query looked just fine:

SELECT
    foo, bar
FROM
    table1
    PIVOT
    (
        AVG(foo) FOR bar IN ([lorem],[ipsum])
    ) ;

But SSMS wouldn't have it. It complained there was an incorrect syntax near ";".

The solution of course was to add the alias for the pivot query, like so:

SELECT
    foo, bar
FROM
    table1
    PIVOT
    (
        AVG(foo) FOR bar IN ([lorem],[ipsum])
    ) AS pivottable