Definition
- PIVOT: Rows to columns. SQL Server: native PIVOT keyword. Others: CASE + GROUP BY.
- UNPIVOT: Columns to rows. Reverse of PIVOT.
- Dynamic PIVOT: Build column list with dynamic SQL when values are unknown at query time.
- Gaps-and-islands: ROW_NUMBER difference technique to find consecutive sequences.
- Conditional aggregation: SUM(CASE WHEN ... THEN ... END) is a portable PIVOT alternative.
- Cross-database: CASE + GROUP BY works everywhere. Native PIVOT is SQL Server only.
This section is a focused tutorial on PIVOT, UNPIVOT, Dynamic SQL, and Advanced Patterns. Each question teaches you to think through the problem before writing SQL, build the query step by step, understand how the database executes it, and optimize for performance.
Understanding the Concepts
## PIVOT, UNPIVOT, Dynamic SQL, and Advanced Patterns
PIVOT transforms rows into columns (e.g., months as column headers). UNPIVOT does the reverse. Dynamic SQL builds query strings at runtime when column names are not known in advance. Advanced patterns include gaps-and-islands, sessionization, running differences, and conditional aggregation with CASE.
Sample Tables
monthly_sales table (shared across all queries in this section):
| employee_id | month | amount | year |
|---|---|---|---|
| 1 | Jan | 50000 | 2026 |
| 1 | Feb | 45000 | 2026 |
| 2 | Jan | 30000 | 2026 |
| 2 | Feb | 35000 | 2026 |
| 3 | Jan | 80000 | 2026 |
| 3 | Feb | 70000 | 2026 |
Other tables from previous sections are also available for advanced pattern questions.
---
Q1. Write a query to pivot rows into columns dynamically (if dynamic pivot is not supported, simulate it for fixed values)
Problem: Write a query to pivot rows into columns dynamically (if dynamic pivot is not supported, simulate it for fixed values).
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT
department_id,
SUM(CASE WHEN job_title = 'Manager' THEN 1
ELSE 0 END) AS Managers,
SUM(CASE WHEN job_title = 'Developer' THEN 1
ELSE 0 END) AS Developers,
SUM(CASE WHEN job_title = 'Tester' THEN 1
ELSE 0 END) AS Testers
FROM employees
GROUP BY department_id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q2. Write a query to pivot monthly sales data for each product into columns
Problem: Write a query to pivot monthly sales data for each product into columns.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT product_id,
SUM(CASE WHEN EXTRACT(MONTH FROM
sale_date) = 1 THEN amount ELSE 0 END) AS Jan,
SUM(CASE WHEN EXTRACT(MONTH FROM
sale_date) = 2 THEN amount ELSE 0 END) AS Feb,
-- Repeat for other months
SUM(CASE WHEN EXTRACT(MONTH FROM
sale_date) = 12 THEN amount ELSE 0 END) AS Dec
FROM sales
GROUP BY product_id;
Step-by-step how the database executes this:
- FROM: Reads table
sale_date. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q3. Write a query to unpivot quarterly sales data into rows
Problem: Write a query to unpivot quarterly sales data into rows.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT product_id, 'Q1' AS quarter, Q1_sales AS
sales FROM sales_data
UNION ALL
SELECT product_id, 'Q2', Q2_sales FROM sales_data
UNION ALL
SELECT product_id, 'Q3', Q3_sales FROM sales_data
UNION ALL
SELECT product_id, 'Q4', Q4_sales FROM sales_data;
Step-by-step how the database executes this:
- FROM: Reads table
sales_data. - SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.
---
Q4. Aggregate JSON data (if supported) to list all employee names in a department as a JSON array
Problem: Aggregate JSON data (if supported) to list all employee names in a department as a JSON array.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT department_id, JSON_AGG(name) AS
employee_names
FROM employees
GROUP BY department_id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q5. Find customers who ordered the most products in
Problem: Find customers who ordered the most products in.
How to think before writing:
- Identify which table has the data I need.
- What columns do I want in the output?
- What condition filters the correct rows?
Pseudo query (plain English first):
GET columns FROM table
SQL Solution:
Find customers who ordered the most products in
Step-by-step how the database executes this:
1. The engine reads the table, evaluates the query, and returns the result.
Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.
---
Key Takeaway
This section covered 5 interview questions on PIVOT, UNPIVOT, Dynamic SQL, and Advanced Patterns. Each question followed the same learning pattern: understand the problem, think about the approach, write a pseudo query in plain English, translate to SQL, understand the execution order, and consider optimization.
PIVOT transforms rows into columns (e.g., months as column headers). UNPIVOT does the reverse. Dynamic SQL builds query strings at runtime when column names are not known in advance. Advanced patterns include gaps-and-islands, sessionization, running differences, and conditional aggregation with CASE.
Practice each query on the shared sample table. If you can solve all 5 without looking at the answer, you have mastered this concept.
---