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.
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.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Static UNPIVOT with UNION ALL | O(r * c) | O(1) | When the table schema is fixed and the number of columns is small |
| Dynamic SQL using information_schema | O(r * c) | O(1) | When columns change frequently or the schema must be handled dynamically |
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 →Practice Dynamic Unpivoting of a Table with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor