Rearrange Products Table - Problem

You are given a Products table with product information stored across multiple store columns. Your task is to rearrange the table to normalize the data structure.

Table: Products

Column NameType
product_idint
store1int
store2int
store3int
  • product_id is the primary key for this table
  • Each row shows a product's price in 3 different stores
  • If a product is not available in a store, the price will be NULL

Goal: Transform the table so each row contains (product_id, store, price). Exclude rows where the product is not available in that store (price is NULL).

Table Schema

Products
Column Name Type Description
product_id PK int Primary key, unique product identifier
store1 int Product price in store1, NULL if not available
store2 int Product price in store2, NULL if not available
store3 int Product price in store3, NULL if not available
Primary Key: product_id
Note: Wide table format with store prices as separate columns

Input & Output

Example 1 — Basic Product Rearrangement
Input Table:
product_id store1 store2 store3
0 95 100
1 110 115
Output:
product_id store price
0 store1 95
0 store2 100
1 store2 110
1 store3 115
💡 Note:

Product 0 is available in store1 (95) and store2 (100) but not store3 (NULL). Product 1 is available in store2 (110) and store3 (115) but not store1 (NULL). The result excludes the NULL entries and creates separate rows for each product-store combination.
Example 2 — Product Available in All Stores
Input Table:
product_id store1 store2 store3
2 50 55 60
Output:
product_id store price
2 store1 50
2 store2 55
2 store3 60
💡 Note:

Product 2 is available in all three stores with different prices. Each store gets its own row in the output, creating three rows from one input row.
Example 3 — Product Available in One Store Only
Input Table:
product_id store1 store2 store3
3 200
Output:
product_id store price
3 store3 200
💡 Note:

Product 3 is only available in store3 with price 200. The NULL values for store1 and store2 are excluded, resulting in only one output row.

Constraints

  • 1 ≤ product_id ≤ 1000
  • Store prices are positive integers when not NULL
  • Each product appears exactly once in the input table

Visualization

Tap to expand
Rearrange Products Table: Column to Row TransformationWide Format (Input)product_idstore1store2store3095100NULL1NULL110115UNION ALLUnpivoting TransformationNormalized Format (Output)product_idstoreprice0store1950store21001store21101store3115NULL values are excluded from the output
Understanding the Visualization
1
Input
Wide table with store columns
2
Unpivot
UNION ALL transformation
3
Output
Normalized rows excluding NULLs
Key Takeaway
🎯 Key Insight: Use UNION ALL to transform columns into rows when unpivoting data from wide to normalized format

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
28.5K Views
Medium Frequency
~12 min Avg. Time
892 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