Building queries using pivot - Don't forget the alias

Peter Schmitz

Administrator
Staff member
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:


Code:
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:

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