How to Create a Pivot Table Using SQL Queries
💡 Concept Name
SQL Pivot Tables – A technique used to transform rows into columns to create summarized and cross-tabulated reports.
📘 Quick Intro
Pivoting in SQL is used to rotate row data into a more readable column format, allowing you to summarize and aggregate information across multiple dimensions like time, category, or region.
🧠 Analogy / Short Story
Think of a school attendance register. Each row is a student's presence each day. A pivot table turns that into a report where each student's name is a row and each day is a column, making it easy to compare attendance across time.
🔧 Technical Explanation
- 🔄 A pivot operation rotates a table by turning unique values from one column into multiple columns.
- 🔢 You typically aggregate values (e.g.,
SUM,COUNT) for each pivoted column. - 📌 SQL Server uses the
PIVOToperator; PostgreSQL usescrosstab()from thetablefuncextension. - 📊 Columns created via pivoting must be known in advance (static pivot); dynamic pivoting uses dynamic SQL.
- 🧮 Useful for generating reports like monthly sales by region or product counts by category.
💻 Real Code Example
-- Example: Total sales per product per month
SELECT *
FROM (
SELECT ProductName, MONTH(OrderDate) AS OrderMonth, SalesAmount
FROM Sales
) AS SourceTable
PIVOT (
SUM(SalesAmount)
FOR OrderMonth IN ([1], [2], [3], [4], [5], [6])
) AS PivotTable;
❓ Interview Q&A
Q1: What is pivoting in SQL?
A: Pivoting transforms row data into columns for summarization and cross-tab reporting.
Q2: Which SQL operator is used for pivoting in SQL Server?
A: The PIVOT operator.
Q3: Can PostgreSQL perform pivoting?
A: Yes, using crosstab() from the tablefunc module.
Q4: What is a limitation of static pivoting?
A: Column values to pivot on must be hard-coded and known beforehand.
Q5: When should you use pivoting?
A: When you want to display aggregated values across multiple dimensions like time or categories.
Q6: Is dynamic pivoting possible?
A: Yes, using dynamic SQL and prepared statements.
Q7: Can you use COUNT in a pivot?
A: Yes, any aggregate like COUNT, SUM, AVG, etc. can be used in a pivot.
Q8: What's an alternative to PIVOT if unsupported?
A: Use CASE statements to simulate pivoting manually.
Q9: Does pivoting affect row granularity?
A: Yes, it compresses multiple rows into one by aggregating values.
Q10: What happens if pivot columns are NULL?
A: The result will display NULL for that cell unless handled using ISNULL/COALESCE.
📝 MCQs
Q1. What is the purpose of a pivot table in SQL?
- Group columns into rows
- Transform rows into columns
- Delete duplicates
- Join multiple tables
Q2. Which SQL clause is used for pivoting in SQL Server?
- GROUP BY
- PIVOT
- CASE
- UNPIVOT
Q3. Which SQL function is used in PostgreSQL to pivot?
- pivot()
- transpose()
- crosstab()
- array_to_table()
Q4. What must be known in advance for static pivoting?
- Primary keys
- All columns
- Pivot column values
- Foreign keys
Q5. Which aggregate is most commonly used in pivoting?
- MIN()
- SUM()
- AVG()
- FIRST()
Q6. Can NULLs appear in pivoted output?
- No
- Yes
- Only in MySQL
- Only when grouped
Q7. What can simulate pivoting in any SQL dialect?
- HAVING
- ROLLUP
- CASE statements
- FULL JOIN
Q8. What does dynamic pivoting require?
- Stored Procedures
- Dynamic SQL
- Triggers
- Subqueries
Q9. Which clause is part of the pivot syntax?
- FROM ... WHERE
- ORDER BY ... LIMIT
- FOR ... IN (...)
- GROUP BY ... ROLLUP
Q10. Can multiple columns be aggregated in a pivot?
- Yes, always
- No, one at a time
- Only COUNT
- Only in PostgreSQL
💡 Bonus Insight
Pivoting is powerful for dashboards and business reports. Dynamic pivots allow adaptable reports but require more SQL logic. Combine pivoting with window functions or CTEs for advanced summaries and analytics.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!