Dynamic Pivoting of a Table - Problem

You are given a Products table that contains product prices in different stores. Your task is to dynamically pivot this table so that each row represents one product and shows its price in each store as separate columns.

The table structure is:

  • product_id (int): The unique identifier for each product
  • store (varchar): The name of the store
  • price (int): The price of the product in that store

You need to implement a procedure PivotProducts that reorganizes the data so that:

  • Each row has one product_id
  • Each store becomes a separate column
  • The value is the price if the product is sold in that store, or NULL if not
  • Store columns should be sorted in lexicographical order

Note: This is a hard problem requiring advanced SQL techniques. There will be at most 30 different stores.

Table Schema

Products
Column Name Type Description
product_id PK int The unique identifier for each product
store PK varchar The name of the store where the product is sold
price int The price of the product in this store
Primary Key: (product_id, store)
Note: Each row indicates the price of a product in a specific store. At most 30 different stores will be present.

Input & Output

Example 1 — Basic Pivot
Input Table:
product_id store price
1 Shop 110
1 LC_Store 100
2 Nozama 200
2 Souq 190
3 Shop 1000
3 Souq 1900
Output:
product_id LC_Store Nozama Shop Souq
1 100 110
2 200 190
3 1000 1900
💡 Note:

The input table has products sold in different stores. After pivoting, each product becomes a row with store names as columns. Store columns are sorted lexicographically: LC_Store, Nozama, Shop, Souq. Product 1 is sold in LC_Store and Shop only, so Nozama and Souq columns are null.
Example 2 — Single Product Multiple Stores
Input Table:
product_id store price
1 A 100
1 B 200
1 C 300
Output:
product_id A B C
1 100 200 300
💡 Note:

Single product sold in three stores A, B, C. All store columns have values since the product is available everywhere. Columns are ordered alphabetically.
Example 3 — Product Not in All Stores
Input Table:
product_id store price
1 Store1 50
2 Store2 75
3 Store1 60
3 Store2 80
Output:
product_id Store1 Store2
1 50
2 75
3 60 80
💡 Note:

Shows products with different store availability. Product 1 only in Store1, Product 2 only in Store2, Product 3 in both stores. NULL values indicate product not sold in that store.

Constraints

  • 1 ≤ product_id ≤ 1000
  • 1 ≤ price ≤ 100000
  • At most 30 different stores
  • store names contain only letters and underscores
  • Each (product_id, store) combination is unique

Visualization

Tap to expand
Dynamic Pivoting: Products → Store ColumnsInput: Productsproduct_idstoreprice1Shop1101LC_Store1002Nozama2002Souq190PIVOTDynamic SQLOutput: Pivoted Tableproduct_idLC_StoreNozamaShopSouq1100null110null2null200null190Key: Stores become columns, sorted alphabetically
Understanding the Visualization
1
Input
Products table with product_id, store, price
2
Pivot
Group by product_id and pivot stores to columns
3
Output
Each row is one product with store columns
Key Takeaway
🎯 Key Insight: Dynamic pivoting transforms row data into columns by generating SQL at runtime based on unique values

Related Problems

Asked in
Amazon 15 Microsoft 12 Oracle 8
15.4K Views
Medium Frequency
~25 min Avg. Time
445 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