Programming Stuff

PIVOT in SQL Server – Correct Aggregated Results

This is my first time using PIVOT and something I learned was: projection ( SELECT) is essential for correct aggregated results.

Here a sample using a Sales table (link to download script file at the bottom):

select-sales

Here a wrong way to do a PIVOT , here the data are not grouped because they are not the same (different Ids)

pivot-wrong-1

If you try to do a projection in the outer SELECT , it won’t work because the unique ids still in the inner SELECT.

pivot-wrong-2

The correct way, it is to SELECT the minimum required columns in the inner query for the PIVOT to group the result.

Now I am happy 🙂 with the correct aggregated result.

pivot-correct

pivot-correct-2

Script: http://jaider.net/wp-content/uploads/2016/09/create-table-and-import-data.zip

Ref: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx