Amey-Thakur Pivot and unpivot
Pivoting turns values into columns, which is what a human-readable report usually wants and what a database is least suited to, because the column list must be known in advance. Unpivoting does the reverse and is usually the healthier direction.
Method
- Pivot with conditional aggregation. A sum or count wrapped in a condition per target column is portable, readable, and works everywhere, whether or not the engine has a PIVOT keyword (see sql-aggregation).
- Fix the column set explicitly. SQL returns a fixed shape, so the categories must be known when the query is written. A changing set means generating the query or reshaping outside the database.
- Decide what an empty cell means. Zero and unknown are different, and the choice between COALESCE to zero and leaving null is a reporting decision, not a technical one (see null-semantics).
- Unpivot by unioning or using the engine's function. Turning wide columns into name and value rows makes the data queryable by period or attribute without rewriting the query for each column.
- Push presentation pivots outward when you can. Reporting tools pivot dynamically and handle changing categories, which SQL cannot do without dynamic query construction.
- Never build dynamic SQL from untrusted input. Generating column names from user data is an injection path (see sql-injection-defense).
Boundaries
- Pivoting is a presentation shape; storing data pivoted makes every future question harder (see database-normalization).
- Wide pivots hit column limits and get slow, since each column is another conditional pass.
- PIVOT syntax exists only in some engines, so conditional aggregation is the portable choice.