Dynamic Pivoting of a Table - Practice Coding Problems

Dynamic Pivoting of a Table - Problem

Database Hard

Dynamic Pivoting of a Table

Imagine you're working with an e-commerce database where products are sold across multiple stores, and you need to transform the data for better analysis. You have a Products table that stores each product-store-price combination as a separate row, but you want to reorganize it into a pivot table format where each row represents one product with columns for each store.

The Challenge: Create a stored procedure PivotProducts that dynamically transforms the table structure. Since store names can vary and change over time, your solution must be dynamic - it should automatically detect all store names and create columns for them.

Table Schema:

Column Name	Type
product_id	int
store	varchar
price	int

Goal: Transform rows into columns where:
• Each row represents one unique product
• Each column represents a store (sorted alphabetically)
• Cell values show the price (or NULL if not sold in that store)
• Maximum of 30 different stores

Input & Output

example_1.sql — Basic Pivot

$ Input: Products table:\n| product_id | store | price |\n|------------|---------|-------|\n| 1 | LCStore | 100 |\n| 1 | Nozama | 200 |\n| 2 | LCStore | 300 |

› Output: | product_id | LCStore | Nozama |\n|------------|---------|--------|\n| 1 | 100 | 200 |\n| 2 | 300 | null |

💡 Note: Product 1 is sold in both stores, so both prices appear. Product 2 is only sold in LCStore, so Nozama column shows null. Stores are ordered alphabetically (LCStore, Nozama).

example_2.sql — Multiple Products and Stores

$ Input: Products table:\n| product_id | store | price |\n|------------|-----------|-------|\n| 1 | Shop | 110 |\n| 1 | LCStore | 100 |\n| 1 | Nozama | 200 |\n| 2 | LCStore | 300 |\n| 3 | Shop | 260 |\n| 3 | Nozama | 270 |

› Output: | product_id | LCStore | Nozama | Shop |\n|------------|---------|--------|------|\n| 1 | 100 | 200 | 110 |\n| 2 | 300 | null | null |\n| 3 | null | 270 | 260 |

💡 Note: Three stores (LCStore, Nozama, Shop) create three columns in alphabetical order. Each product shows its price per store, with null for stores where it's not sold.

example_3.sql — Edge Case: Single Store

$ Input: Products table:\n| product_id | store | price |\n|------------|---------|-------|\n| 1 | OnlyOne | 100 |\n| 2 | OnlyOne | 200 |

› Output: | product_id | OnlyOne |\n|------------|---------|\n| 1 | 100 |\n| 2 | 200 |

💡 Note: When there's only one store, the pivot still works correctly, creating a single store column. This demonstrates the dynamic nature of the solution.

Visualization

Tap to expand

Understanding the Visualization

Analyze Original Structure

Each row represents one product sold at one store with its price

Identify Unique Stores

Scan through all data to find every unique store name

Create Column Headers

Sort store names alphabetically and prepare them as column headers

Transform Data

Group by product and spread store prices across columns

Key Takeaway

🎯 Key Insight: Dynamic SQL enables runtime schema adaptation, making the pivot operation truly flexible for changing business requirements while maintaining optimal performance.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Two passes: first to discover stores O(n), then to execute pivot query O(n)

✓ Linear Growth

Space Complexity

O(s + p)

Where s is number of unique stores and p is number of unique products

✓ Linear Space

Constraints

1 ≤ product_id ≤ 10⁴
1 ≤ price ≤ 10⁴
There will be at most 30 different stores in the table
store names are valid SQL identifiers
Advanced SQL knowledge required - dynamic SQL and stored procedures

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 ◯ Oracle 28

The optimal solution uses dynamic SQL to automatically discover store names and generate a pivot query at runtime. The key insight is that traditional pivot operations require knowing column names at compile time, but dynamic SQL allows us to build and execute queries with runtime-discovered columns. The procedure first queries for unique stores (sorted alphabetically), constructs CASE statements for each store, then executes the complete pivot query to transform rows into columns.

Common Approaches

Approach	Time	Space	Notes
✓ Static Pivot with Fixed Columns	O(n)	O(k)	Hardcode store names and use static CASE statements
Dynamic SQL with Store Discovery (Optimal)	O(n)	O(s + p)	Dynamically discover store names and build pivot query using dynamic SQL

Static Pivot with Fixed Columns — Algorithm Steps

Identify all unique store names manually
Write CASE statements for each known store
Group by product_id and aggregate prices
Handle NULL values for missing store-product combinations

Visualization

Tap to expand

Step-by-Step Walkthrough

Original Data

Each product-store combination is a separate row

Group by Product

Group all rows by product_id

Apply CASE Logic

Use CASE statements to create columns for each store

Pivot Result

Each product becomes one row with store columns

Code -

solution.c — C

// Static Pivot Approach (Not truly dynamic)
// This demonstrates the concept but doesn't solve the dynamic requirement

#include <stdio.h>
#include <string.h>

const char* staticPivotDemo() {
    /*
    This demonstrates the concept but doesn't solve the dynamic requirement.
    In real SQL, you'd use static CASE statements.
    */
    return "\n"
        "SELECT \n"
        "    product_id,\n"
        "    MAX(CASE WHEN store = 'Store1' THEN price END) AS Store1,\n"
        "    MAX(CASE WHEN store = 'Store2' THEN price END) AS Store2,\n"
        "    MAX(CASE WHEN store = 'Store3' THEN price END) AS Store3\n"
        "FROM Products\n"
        "GROUP BY product_id\n"
        "ORDER BY product_id;\n";
}

int main() {
    printf("%s", staticPivotDemo());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through the Products table to group and aggregate

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique products in the result set

✓ Linear Space

Constraints

1 ≤ product_id ≤ 10⁴
1 ≤ price ≤ 10⁴
There will be at most 30 different stores in the table
store names are valid SQL identifiers
Advanced SQL knowledge required - dynamic SQL and stored procedures

24.6K Views

Medium Frequency

~35 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Static Pivot with Fixed Columns — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler