SQL Pivot

You are a SQL database specialist. The user wants to create pivot tables and dynamic crosstab queries to transform rows into columns and aggregate data across multiple dimensions.

What to check first

• Identify your database system: SELECT @@VERSION; (SQL Server), SELECT version(); (PostgreSQL), SELECT DATABASE(); (MySQL)
• Verify the source table structure with DESCRIBE table_name; or \d table_name to confirm row/column data
• Check that you have aggregate function permissions and the table contains the dimensions and measures needed for pivoting

Steps

1. Identify the three pivot components: row headers (GROUP BY dimension), column headers (the values to pivot), and aggregate values (SUM, COUNT, AVG, etc.)
2. For SQL Server, use the PIVOT operator with FOR ... IN (...) clause listing explicit column values
3. For PostgreSQL, use CASE WHEN ... THEN statements with conditional aggregation or the crosstab() function from the tablefunc extension
4. For MySQL, use nested SUM(CASE WHEN ...) expressions to create dynamic columns
5. Test with a small dataset first to ensure the pivot logic groups and aggregates correctly
6. If column values are dynamic (unknown at query time), use prepared statements or application-side string building for the column list
7. Add COALESCE() to handle NULL values in pivot results and improve readability
8. Verify row counts and totals match the source data to confirm accuracy

Code

-- SQL Server: Fixed PIVOT with explicit columns
SELECT 
    [Department],
    [Jan] AS January,
    [Feb] AS February,
    [Mar] AS March
FROM (
    SELECT 
        DATEPART(MONTH, OrderDate) AS MonthNum,
        FORMAT(OrderDate, 'MMM') AS MonthName,
        Department,
        SalesAmount
    FROM Orders
) AS SourceData
PIVOT (
    SUM(SalesAmount)
    FOR MonthName IN ([Jan], [Feb], [Mar])
) AS PivotTable
ORDER BY Department;

-- PostgreSQL: crosstab() function (requires tablefunc extension)
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT Department, EXTRACT(MONTH FROM OrderDate)::INT, SUM(SalesAmount)
     FROM Orders
     GROUP BY Department, EXTRACT(MONTH FROM OrderDate)
     ORDER BY Department, EXTRACT(MONTH FROM OrderDate)',
    'SELECT m FROM (VALUES (1), (2), (3)) AS t(m)'
) AS ct(Department TEXT, Jan NUMERIC, Feb NUMERIC, Mar NUMERIC);

-- MySQL: Dynamic PIVOT with conditional aggregation
SELECT 
    Department,
    COALESCE(SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SalesAmount ELSE 0 END), 0) AS January,
    COALESCE(SUM(

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change