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: Wide to Narrow TransformationWide Format (Input)product_idstore1store2store311015NULL2NULL2025Dynamic SQL Process1. Query information_schema2. Build UNION ALL query3. Filter NULL valuesNarrow Format (Output)product_idstoreprice1store1101store2152store2202store325
Understanding the Visualization
1
Wide Input
Products table with dynamic store columns
2
Dynamic Detection
Query system tables to find column names
3
Narrow Output
Unpivoted table with product_id, store, price
Key Takeaway
🎯 Key Insight: Dynamic SQL enables handling variable table schemas by querying metadata tables

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