Skip to main content
Talentd

Dynamic Unpivoting of a Table - Solution & Explanation

HardPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store_name1 | int     |
| store_name2 | int     |
|      :      | int     |
|      :      | int     |
|      :      | int     |
| store_namen | int     |
+-------------+---------+
product_id is the primary key for this table.
Each row in this table indicates the product's price in n different stores.
If the product is not available in a store, the price will be null in that store's column.
The names of the stores may change from one testcase to another. There will be at least 1 store and at most 30 stores.

 

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 UnpivotProducts to reorganize the Products table so that each row has the id of one product, the name of a store where it is sold, and its price in that store. If a product is not available in a store, do not include a row with that product_id and store combination in the result table. There should be three columns: product_id, store, and price.

The procedure should return the table after reorganizing it.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Products table:
+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1          | 100      | null   | 110  | null |
| 2          | null     | 200    | null | 190  |
| 3          | null     | null   | 1000 | 1900 |
+------------+----------+--------+------+------+
Output: 
+------------+----------+-------+
| product_id | store    | price |
+------------+----------+-------+
| 1          | LC_Store | 100   |
| 1          | Shop     | 110   |
| 2          | Nozama   | 200   |
| 2          | Souq     | 190   |
| 3          | Shop     | 1000  |
| 3          | Souq     | 1900  |
+------------+----------+-------+
Explanation: 
Product 1 is sold in LC_Store and Shop with prices of 100 and 110 respectively.
Product 2 is sold in Nozama and Souq with prices of 200 and 190.
Product 3 is sold in Shop and Souq with prices of 1000 and 1900.

Approach Overview

Problem Overview: The table contains multiple value columns that must be converted into a row-wise format. Instead of manually listing every column, the query must dynamically discover column names and unpivot them so each column becomes a row with its corresponding value.

Approach 1: Static UNPIVOT using UNION ALL (Time: O(r * c), Space: O(1))

The straightforward approach manually converts columns into rows using repeated SELECT ... UNION ALL statements. For every column, select the identifier column and the column value while labeling the column name as a new attribute. The database scans the table once per column, so runtime scales with the number of rows r and columns c. This works well when the schema is fixed and the number of columns is small, but it breaks when new columns are added because the SQL must be rewritten.

Approach 2: Dynamic SQL with Metadata (Time: O(r * c), Space: O(1))

The scalable solution queries information_schema.columns to retrieve column names dynamically. Using GROUP_CONCAT, build a string that generates a SELECT ... UNION ALL statement for each column except the primary identifier. That generated SQL string forms a complete unpivot query. MySQL then executes it using PREPARE and EXECUTE. The key insight: metadata tables allow you to programmatically inspect schema structure, so the query adapts automatically when columns change. This pattern appears frequently in advanced SQL transformations and reporting pipelines.

Because each generated subquery still scans the table once, the runtime remains proportional to rows multiplied by columns. However, you gain schema flexibility and eliminate manual query maintenance. This is the standard method for dynamic transformations in database systems that do not provide a built-in UNPIVOT operator.

Recommended for interviews: Interviewers expect the dynamic metadata approach. Showing the static UNION ALL version demonstrates you understand how unpivoting works internally. Building the dynamic query using information_schema, GROUP_CONCAT, and prepared statements shows strong practical knowledge of MySQL and real-world data transformation patterns.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Static UNPIVOT with UNION ALLO(r * c)O(1)When the table schema is fixed and the number of columns is small
Dynamic SQL using information_schemaO(r * c)O(1)When columns change frequently or the schema must be handled dynamically

Video Solution

Convert Columns to Rows in Excel (NO Transpose, NO Formulas - SIMPLY UNPIVOT in Power Query) • Leila Gharani • 531,195 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Dynamic Unpivoting of a Table easy or hard?
The problem is rated Hard because it requires understanding dynamic SQL generation and database metadata tables. Basic SQL users know UNION ALL, but building queries dynamically from information_schema requires deeper database knowledge.
Dynamic Unpivoting of a Table Python/Java solution
This problem is SQL-focused rather than language-specific. The core logic is implemented in MySQL using GROUP_CONCAT, information_schema, and prepared statements. Application code in Python or Java would simply execute the generated SQL query against the database.
How to solve Dynamic Unpivoting of a Table in O(n)?
Database engines process rows column by column during unpivot operations, so the practical complexity is O(r * c). The optimal solution minimizes manual work by dynamically generating the UNION ALL query using metadata from information_schema and executing it with prepared statements.
What is the best approach for Dynamic Unpivoting of a Table?
The most practical solution uses dynamic SQL built from information_schema metadata. Column names are collected with GROUP_CONCAT to generate a UNION ALL query automatically. This removes the need to manually list columns and adapts when the table schema changes.
Is Dynamic Unpivoting of a Table asked at Google/Amazon/Meta?
Dynamic SQL and table transformation questions appear in data engineering and backend SQL interviews at companies like Amazon, Meta, and Google. Problems involving pivot and unpivot operations test real-world SQL knowledge used in analytics pipelines.
What data structure is used in Dynamic Unpivoting of a Table?
The solution relies on relational database metadata tables such as information_schema.columns. These tables act as schema catalogs that allow queries to discover column names dynamically and generate SQL strings for execution.
What is the time complexity of Dynamic Unpivoting of a Table?
The effective runtime is O(r * c), where r is the number of rows and c is the number of columns being unpivoted. Each generated SELECT scans the table once for a column, so total work scales with rows multiplied by columns.

Ready to solve this problem?

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

Practice on FleetCode
Previous

Rearrange Products Table