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):
Here a wrong way to do a PIVOT , here the data are not grouped because they are not the same (different Ids)
If you try to do a projection in the outer SELECT , it won’t work because the unique ids still in the inner SELECT.
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.