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 of a Table INPUT: Products Table product_id store price 1 Store1 100 1 Store2 110 2 Store1 200 2 Store3 210 3 Store2 300 Vertical format: Each row = one price in one store Unique Stores: Store1 Store2 Store3 ALGORITHM STEPS 1 Get Unique Stores SELECT DISTINCT store ORDER BY store ASC 2 Build Dynamic SQL Generate CASE statements for each store column 3 Create Pivot Query GROUP BY product_id MAX(CASE WHEN...) 4 Execute Statement PREPARE and EXECUTE dynamic SQL query -- Dynamic SQL Pattern SELECT product_id, MAX(CASE store WHEN 'X' THEN price END) AS X... FINAL RESULT product_id Store1 Store2 Store3 1 100 110 NULL 2 200 NULL 210 3 NULL 300 NULL OK - Pivoted! Each product = 1 row Stores = columns (sorted) Transformation: Vertical 5 rows Horizontal 3 rows x 4 cols Key Insight: Dynamic pivoting requires building SQL at runtime because column names (stores) are not known until query time. Use GROUP_CONCAT or STRING_AGG to build CASE expressions, then PREPARE/EXECUTE for the dynamic query. TutorialsPoint - Dynamic Pivoting of a Table | Optimal Solution

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