Pivot and unpivot concepts in SQL - Time & Space Complexity

Time Complexity: Pivot and unpivot concepts

O(n)

Understanding Time Complexity

When using pivot and unpivot in SQL, we want to know how the time to run the query changes as the data grows.

We ask: How does the work increase when there are more rows or columns?

Scenario Under Consideration

Analyze the time complexity of the following SQL pivot operation.

SELECT Product, 
       SUM(CASE WHEN Year = 2022 THEN Sales ELSE 0 END) AS Sales_2022,
       SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS Sales_2023
FROM SalesData
GROUP BY Product;
    

This query turns rows of sales data by year into columns for each year, grouped by product.

Identify Repeating Operations

Look for repeated work inside the query.

• Primary operation: Scanning all rows in the SalesData table.
• How many times: Once over all rows, but with conditional checks for each year column.

How Execution Grows With Input

As the number of rows grows, the query must check each row once.

Input Size (n)	Approx. Operations
10	About 10 row checks
100	About 100 row checks
1000	About 1000 row checks

Pattern observation: The work grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the pivot grows in a straight line as the data size grows.

Common Mistake

[X] Wrong: "Pivoting makes the query run much faster because it groups data into columns."

[OK] Correct: Pivoting changes how data looks but still needs to scan all rows, so the time depends on data size, not just format.

Interview Connect

Understanding how pivot and unpivot scale helps you explain query performance clearly and shows you know how data size affects work done.

Self-Check

"What if we added more years to pivot on? How would that affect the time complexity?"