Skip to main content
Talentd

Dynamic Pivoting of a Table - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min read
Practice this problem

Problem Statement

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store       | varchar |
| price       | int     |
+-------------+---------+
(product_id, store) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of product_id in store.
There will be at most 30 different stores in the table.
price is the price of the product at this store.

 

Important note: This problem targets those who have a good experience with SQL. If you are a beginner, we recommend that you skip it for now.

Implement the procedure PivotProducts to reorganize the Products table so that each row has the id of one product and its price in each store. The price should be null if the product is not sold in a store. The columns of the table should contain each store and they should be sorted in lexicographical order.

The procedure should return the table after reorganizing it.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Products table:
+------------+----------+-------+
| product_id | store    | price |
+------------+----------+-------+
| 1          | Shop     | 110   |
| 1          | LC_Store | 100   |
| 2          | Nozama   | 200   |
| 2          | Souq     | 190   |
| 3          | Shop     | 1000  |
| 3          | Souq     | 1900  |
+------------+----------+-------+
Output: 
+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1          | 100      | null   | 110  | null |
| 2          | null     | 200    | null | 190  |
| 3          | null     | null   | 1000 | 1900 |
+------------+----------+--------+------+------+
Explanation: 
We have 4 stores: Shop, LC_Store, Nozama, and Souq. We first order them lexicographically to be: LC_Store, Nozama, Shop, and Souq.
Now, for product 1, the price in LC_Store is 100 and in Shop is 110. For the other two stores, the product is not sold so we set the price as null.
Similarly, product 2 has a price of 200 in Nozama and 190 in Souq. It is not sold in the other two stores.
For product 3, the price is 1000 in Shop and 1900 in Souq. It is not sold in the other two stores.

Approach Overview

Problem Overview: You are given a table where each row stores a product_id, a store name, and its price. The goal is to pivot the table so each store becomes a separate column and every product appears once with its price in each store column. The challenge is that the list of stores is not fixed, so the pivot must be generated dynamically.

Approach 1: Static Pivot Using CASE Aggregation (O(n * m) time, O(1) extra space)

If the set of stores is known ahead of time, you can pivot the table using conditional aggregation. For each store, create an expression like MAX(CASE WHEN store = 'StoreA' THEN price END). Group the results by product_id. This works because the CASE expression selects the price only for matching rows, and MAX collapses the grouped rows into a single value. The database scans n rows and evaluates conditions across m stores, giving roughly O(n * m) time complexity and constant auxiliary space. The downside is maintainability—every new store requires changing the query.

Approach 2: Dynamic Pivot with GROUP_CONCAT and Prepared Statements (O(n + m) time, O(m) space)

When store names are not known beforehand, build the pivot query dynamically. First, scan the table to collect distinct store values and construct conditional aggregation expressions using GROUP_CONCAT. Each generated expression looks like MAX(CASE WHEN store = 'X' THEN price END) AS `X`. The concatenated string becomes part of a dynamic SQL query that groups by product_id. Execute it using PREPARE and EXECUTE. The initial step processes all rows once to discover stores, then the final pivot query scans the dataset again. The total complexity is roughly O(n + m) with O(m) memory for the generated column list.

This method is common in reporting systems where schema flexibility matters. It leverages features specific to SQL engines like MySQL. The heavy lifting happens in aggregation rather than application code, which keeps the solution concise and efficient for database-centric workflows.

Recommended for interviews: Start by explaining the static pivot using CASE and GROUP BY. It demonstrates understanding of conditional aggregation. Then move to the dynamic pivot using GROUP_CONCAT and prepared SQL statements. Interviewers typically expect the dynamic approach because the problem explicitly requires handling unknown columns at runtime.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Static Pivot with CASE AggregationO(n * m)O(1)When all store columns are known beforehand and rarely change
Dynamic Pivot with GROUP_CONCAT + Prepared SQLO(n + m)O(m)When pivot columns are unknown and must be generated dynamically at runtime

Video Solution

8 patterns to solve 80% Leetcode problems • Sahil & Sarra • 656,651 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Dynamic Pivoting of a Table easy or hard?
Dynamic Pivoting of a Table is considered a hard SQL problem because it requires constructing queries dynamically. Developers must combine conditional aggregation, GROUP_CONCAT string building, and prepared statements, which goes beyond basic SELECT and GROUP BY usage.
Dynamic Pivoting of a Table Python/Java solution
The problem itself is SQL-based, typically solved in MySQL using dynamic queries. In application code like Python or Java, you could fetch rows and build a dictionary or map keyed by product_id with nested keys for store names, then output the pivoted structure. That approach has O(n) processing time but moves the pivot logic outside the database.
How to solve Dynamic Pivoting of a Table in O(n)?
A near O(n) solution uses a single aggregation query after generating column expressions dynamically. Use GROUP_CONCAT to construct MAX(CASE WHEN store = 'X' THEN price END) for each store, then group by product_id. The database processes rows sequentially while evaluating conditional aggregates.
What is the best approach for Dynamic Pivoting of a Table?
The most practical solution uses dynamic SQL with GROUP_CONCAT and conditional aggregation. First collect distinct pivot values (stores) using GROUP_CONCAT to generate MAX(CASE WHEN ...) expressions. Then execute the generated query using PREPARE and EXECUTE. This handles unknown columns dynamically and runs in roughly O(n + m) time where n is rows and m is distinct stores.
Is Dynamic Pivoting of a Table asked at Google/Amazon/Meta?
Dynamic pivoting problems appear in SQL interview rounds for data-focused roles at companies like Amazon, Meta, and analytics-heavy teams at Google. They test understanding of GROUP BY, conditional aggregation, and how to reshape relational data without fixed schemas.
What data structure is used in Dynamic Pivoting of a Table?
The solution relies on relational database aggregation rather than traditional in-memory data structures. Internally the database uses hash or sort-based grouping for GROUP BY operations while evaluating CASE expressions for each pivot column.
What is the time complexity of Dynamic Pivoting of a Table?
The dynamic pivot approach typically runs in O(n + m) time. The database scans n rows to discover distinct pivot values and build expressions, then executes the final grouped query. Space complexity is O(m) because the generated SQL string stores one aggregation expression per pivot column.

Ready to solve this problem?

Practice Dynamic Pivoting of a Table with our built-in code editor and test cases.

Practice on FleetCode
Previous

Product's Price for Each Store