Pivoting transforms grouped row values into columns while aggregating associated values. For a stock report, it turns product categories from vertical rows into one horizontal result. Mistake #1: using a cursor because the output columns appear to require row-by-row updates. Cursors impose repeated statement overhead and are inefficient for relational data, especially at large row counts. section 5.7
Prefer a set-based PIVOT query when the report has known category columns:
- Avoid cursor-based temporary-table updates unless a specific constraint proves necessary; the set-based form is shorter and avoids per-row processing. section 5.7
MagicChoc stock report
A stock query groups product categories and feeds ProductCategoryName and Stock into PIVOT, producing columns such as Raw Ingredients, Machine Parts, and Misc with summed quantities.
