Dynamic Unpivoting of a Table - Problem

Given a Products table with dynamic store columns, implement a procedure UnpivotProducts to reorganize the data.

The table has a product_id column and multiple store columns (store_name1, store_name2, etc.) containing prices. Each store column contains the price of the product in that store, or NULL if not available.

Requirements:

  • Transform the wide table format into a narrow format
  • Each row should contain: product_id, store, and price
  • Exclude rows where the product is not available in a store (price is NULL)
  • Handle dynamic column names that may change between test cases
  • Store names can be any valid column name

Table Schema

Products
Column Name Type Description
product_id PK int Primary key identifying the product
store1 int Price in store1, NULL if not available
store2 int Price in store2, NULL if not available
store3 int Price in store3, NULL if not available
Primary Key: product_id
Note: Store column names are dynamic and may vary. There can be 1-30 store columns.

Input & Output

Example 1 — Basic Unpivoting
Input Table:
product_id store1 store2 store3
1 10 15
2 20 25
Output:
product_id store price
1 store1 10
1 store2 15
2 store2 20
2 store3 25
💡 Note:

The wide table with store columns is unpivoted into a narrow format. Product 1 is available in store1 (price 10) and store2 (price 15) but not in store3, so no row for that combination. Product 2 is available in store2 and store3 but not store1.
Example 2 — All NULL Prices
Input Table:
product_id store1 store2
3
Output:
product_id store price
💡 Note:

When a product has NULL prices in all stores, no rows are included in the output since the product is not available anywhere.
Example 3 — Single Store Column
Input Table:
product_id amazon
1 100
2 200
Output:
product_id store price
1 amazon 100
2 amazon 200
💡 Note:

Even with just one store column, the dynamic approach works correctly, transforming the data into the required format with store name as a value.

Constraints

  • 1 ≤ product_id ≤ 1000
  • 1 ≤ number of stores ≤ 30
  • 0 ≤ price ≤ 1000 or NULL
  • Store column names are dynamic and can vary between test cases

Visualization

Tap to expand
Dynamic Unpivoting of a Table INPUT: Wide Table product_id store1 store2 store3 0 95 100 105 1 70 NULL 80 Dynamic Store Columns [store1, store2, store3, ...] Products Table CREATE TABLE Products ( product_id INT, store1 INT, store2 INT... ); Columns vary per table ALGORITHM STEPS 1 Get Column Names Query INFORMATION_SCHEMA to find all store columns 2 Build UNION Queries For each store column: SELECT product_id, 'storeN' AS store, storeN 3 Combine with UNION Join all SELECT statements with UNION ALL operator 4 Filter NULLs WHERE price IS NOT NULL Excludes unavailable items Execute Dynamic SQL PREPARE + EXECUTE FINAL RESULT product_id store price 0 store1 95 0 store2 100 0 store3 105 1 store1 70 1 store3 80 NULL values filtered: product_id=1, store2 Narrow Format Each row = one product in one store with price OK Unpivot Complete Key Insight: Dynamic unpivoting requires querying the schema at runtime to handle variable column counts. Build SQL strings dynamically using INFORMATION_SCHEMA.COLUMNS, then execute with PREPARE/EXECUTE. UNION ALL combines each store column into rows. Filter WHERE price IS NOT NULL removes unavailable products. TutorialsPoint - Dynamic Unpivoting of a Table | Optimal Solution

Related Problems

Asked in
Meta 28 Amazon 22 Google 18
23.4K Views
Medium Frequency
~25 min Avg. Time
847 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen