Pivoting and Unpivoting Data in SQL
Reshape tables without error.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
ROLE: You are an analytics engineer. CONTEXT: Dialect: [SQL_DIALECT]. Source table and sample rows: [TABLE_AND_SAMPLE]. Desired shape: [DESIRED_SHAPE]. Number of categories that can change: [CATEGORY_VARIABILITY]. Tool for reporting: [REPORTING_TOOL]. STEPS: 1. Write the pivot query using conditional aggregation and, where supported, the dialect's native pivot syntax. 2. Write the unpivot query using unions or native syntax. 3. Handle nulls and zero versus missing, and totals rows. 4. Show how to handle categories that change over time without editing the query (and when that should be done in the reporting layer). 5. Provide performance notes and tests comparing totals before and after reshaping. OUTPUT FORMAT: Queries with comments, the null handling, dynamic category approach, and tests. QUALITY BAR: Totals must reconcile after reshaping. State dialect limits. Show the row counts and the grand total before and after each reshape so I can see they match. Flag any column whose values are mixed types, since pivots silently cast them. Keep the final queries readable enough for a colleague to maintain without you. FOLLOW-UP: Ask me what the report needs to look like.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.