Dynamic Unpivoting of a Table - Practice Coding Problems

Dynamic Unpivoting of a Table - Problem

Database Hard

Transform a Product Pricing Table - The Ultimate SQL Unpivoting Challenge!

You're given a dynamic pivot table containing product prices across multiple stores. Your mission: transform this wide table into a normalized format that's much easier to work with!

The Challenge:
• Input: A table with product_id and multiple store columns (store_name1, store_name2, ..., store_namen)
• Each store column contains the product's price in that store (or NULL if not available)
• The number and names of store columns vary between test cases
• Output: A clean table with exactly 3 columns: product_id, store, and price

🎯 Goal: Create a stored procedure UnpivotProducts that dynamically unpivots any table structure and excludes NULL prices.

Why This Matters: This is a real-world scenario where data comes in pivot format but you need it normalized for analysis, reporting, or further processing. Master this and you'll handle any data transformation challenge!

Input & Output

Basic Example - Multiple Stores

$ Input: Products table: +------------+--------+--------+--------+ | product_id | LC_Store| Nozama | Shop | +------------+--------+--------+--------+ | 0 | 95 | 100 | 105 | | 1 | 70 | NULL | 80 | +------------+--------+--------+--------+

› Output: +------------+----------+-------+ | product_id | store | price | +------------+----------+-------+ | 0 | LC_Store | 95 | | 0 | Nozama | 100 | | 0 | Shop | 105 | | 1 | LC_Store | 70 | | 1 | Shop | 80 | +------------+----------+-------+

💡 Note: Product 0 is available in all 3 stores, so we get 3 rows. Product 1 is only available in LC_Store and Shop (Nozama price is NULL), so we get 2 rows. Notice how NULL values are excluded from the result.

Edge Case - Single Store

$ Input: Products table: +------------+---------+ | product_id | Store1 | +------------+---------+ | 0 | 10 | | 1 | 25 | +------------+---------+

› Output: +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 0 | Store1 | 10 | | 1 | Store1 | 25 | +------------+--------+-------+

💡 Note: Even with just one store column, the unpivoting process works correctly. Each product gets one row with the store name and price.

Edge Case - All NULL Prices

$ Input: Products table: +------------+--------+--------+ | product_id | Store1 | Store2 | +------------+--------+--------+ | 0 | NULL | NULL | | 1 | 50 | NULL | +------------+--------+--------+

› Output: +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 1 | Store1 | 50 | +------------+--------+-------+

💡 Note: Product 0 has NULL prices in all stores, so it doesn't appear in the result. Product 1 only has a price in Store1, so we get exactly one row. This demonstrates proper NULL handling.

Constraints

1 ≤ Number of products ≤ 1000
1 ≤ Number of stores ≤ 30
Store names can be any valid column name
Product prices are positive integers or NULL
product_id is always the primary key column

Visualization

Tap to expand

Understanding the Visualization

Wide Format Input

Products table with multiple store columns (pivot format)

Column Discovery

INFORMATION_SCHEMA identifies all store columns dynamically

Query Construction

Build UNION ALL query for each store column with NULL filtering

Execution

Execute dynamic SQL using prepared statements

Normalized Output

Clean table with product_id, store, and price columns

Key Takeaway

🎯 Key Insight: By leveraging SQL metadata and dynamic query construction, we can create a truly flexible unpivoting solution that adapts to any table structure automatically.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses dynamic SQL with INFORMATION_SCHEMA to automatically discover store column names at runtime. This approach queries the database metadata to find all columns except product_id, then constructs a UNION ALL query dynamically. The key insight is leveraging SQL's prepared statements to execute the dynamically built query, making it fully adaptive to any table structure without manual maintenance.

Common Approaches

Approach	Time	Space	Notes
✓ Manual Column Enumeration	O(n*m)	O(n*m)	Hardcode each store column in a UNION statement
Dynamic SQL with Information Schema (Optimal)	O(n*m)	O(n*m)	Use INFORMATION_SCHEMA to dynamically discover columns and build unpivot query

Manual Column Enumeration — Algorithm Steps

Write separate SELECT statements for each known store column
Use UNION ALL to combine all SELECT statements
Filter out NULL values with WHERE clause
Return the combined result set

Visualization

Tap to expand

Step-by-Step Walkthrough

Identify Stores

Manually list all known store column names

Create UNION Query

Write separate SELECT for each store column

Filter NULLs

Add WHERE clause to exclude NULL prices

Combine Results

UNION ALL combines all store results

Code -

solution.c — C

// Manual approach - not truly dynamic
#include <stdio.h>
#include <string.h>

char* unpivot_products_manual() {
    static char query[] = 
        "CREATE PROCEDURE UnpivotProducts()\n"
        "BEGIN\n"
        "    SELECT product_id, 'store_name1' as store, store_name1 as price\n"
        "    FROM Products \n"
        "    WHERE store_name1 IS NOT NULL\n"
        "    \n"
        "    UNION ALL\n"
        "    \n"
        "    SELECT product_id, 'store_name2' as store, store_name2 as price\n"
        "    FROM Products \n"
        "    WHERE store_name2 IS NOT NULL;\n"
        "    -- ... continue for all possible stores\n"
        "END;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n*m)

n products × m stores, but requires manual coding for each store

✓ Linear Growth

Space Complexity

O(n*m)

Result set size is number of non-null product-store combinations

⚡ Linearithmic Space

58.0K Views

Medium Frequency

~35 min Avg. Time

2.2K 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

Constraints

Visualization

Related Problems

Common Approaches

Manual Column Enumeration — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler