Back to SQL Practice: 331 Real Interview Queries
Q9Question 9 of 9

PIVOT, UNPIVOT, Dynamic SQL, and Advanced Patterns

Answer: PIVOT transforms rows into columns (months as headers), UNPIVOT reverses it, and dynamic SQL builds the pivot column list at runtime when values are unknown.

Key Concepts
  • PIVOT transforms rows into columns. Example: monthly sales rows become columns Jan, Feb, Mar with amount as values.
  • SQL Server has native PIVOT syntax. PostgreSQL and MySQL use CASE + GROUP BY: SUM(CASE WHEN month = 'Jan' THEN amount END) AS Jan.
  • UNPIVOT transforms columns into rows. Reverse of PIVOT. Useful for normalizing denormalized data.
  • Dynamic PIVOT builds the column list at runtime using dynamic SQL when you do not know the column values in advance.
  • Gaps-and-islands: find consecutive sequences or gaps in data. Use ROW_NUMBER difference technique: date - ROW_NUMBER() gives the same value for consecutive dates.
  • Conditional aggregation with CASE inside SUM/COUNT is the portable alternative to PIVOT that works on all databases.

Definition

PIVOT transforms rows into columns (months as headers), UNPIVOT reverses it, and dynamic SQL builds the pivot column list at runtime when values are unknown.
  • 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_idmonthamountyear
1Jan500002026
1Feb450002026
2Jan300002026
2Feb350002026
3Jan800002026
3Feb700002026

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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
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:

  1. FROM: Reads table sale_date.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table sales_data.
  2. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, JSON_AGG(name) AS 
employee_names 
FROM employees 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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:

  1. Identify which table has the data I need.
  2. What columns do I want in the output?
  3. What condition filters the correct rows?

Pseudo query (plain English first):

TEXT
GET columns FROM table

SQL Solution:

SQL
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.

---

Share
Use ← → arrow keys to navigate