Dynamic Unpivoting of a Table

Problem

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store_name1 | int     |
| store_name2 | int     |
|      :      | int     |
|      :      | int     |
|      :      | int     |
| store_namen | int     |
+-------------+---------+
product_id is the primary key for this table.
Each row in this table indicates the product's price in n different stores.
If the product is not available in a store, the price will be null in that store's column.
The names of the stores may change from one testcase to another. There will be at least 1 store and at most 30 stores.

Problem Definition

Implement the procedure UnpivotProducts to reorganize the Products table so that each row has the id of one product, the name of a store where it is sold, and its price in that store. If a product is not available in a store, do not include a row with that product_id and store combination in the result table. There should be three columns: product_id, store, and price.

The procedure should return the table after reorganizing it.

Return the result table in any order.

Example

Output

Solution

To transform the Products table from a wide format—where each store has its own column—to a long format—where each row represents a product's price in a specific store—we implement the stored procedure UnpivotProducts. This procedure dynamically reorganizes the data, ensuring flexibility regardless of the number or names of stores involved. Below is a comprehensive breakdown of the approach, step-by-step execution, and detailed explanations for each component of the provided SQL query.

Approach Overview

1. Understand the Data Structure:

   • Wide Format: The Products table has multiple columns for each store (store_name1, store_name2, ..., store_namen), each representing the price of the product in that store.
   • Long Format: The desired output has three columns: product_id, store, and price, with each row representing a product's price in a specific store.

2. Identify Store Columns Dynamically:

   • Since the number and names of stores can vary across different test cases (up to 30 stores), the procedure must dynamically identify all store columns in the Products table.

3. Construct Dynamic SQL for Unpivoting:

   • Use SQL string functions to build a dynamic SELECT statement that unpivots the data by converting each store column into separate rows.

4. Execute the Dynamic SQL:

   • Prepare and execute the dynamically constructed SQL statement to generate the final unpivoted table.

SQL Query

CREATE PROCEDURE UnpivotProducts()
BEGIN
	# Write your MySQL query statement below.
	set group_concat_max_len = 1000000;
	set @sql = null;
	with stores as (
	    SELECT COLUMN_NAME store 
	    FROM INFORMATION_SCHEMA.COLUMNS 
	    WHERE TABLE_NAME='Products' 
	      and COLUMN_NAME<>'product_id'
	)
	select group_concat(
	    concat(
	        'select product_id, "', store, '" ', 
	        'as store, ', store, ' ', 
	        'as price from Products where ', store, ' is not null union'
	    ) 
	    order by store 
	    separator ' '
	) into @sql
	from stores;

	/*select SUBSTRING(@sql, 1, LENGTH(@sql)-6);*/
	set @sql = SUBSTRING(@sql, 1, LENGTH(@sql)-6);
	prepare stmt from @sql;
	execute stmt;
END

Step-by-Step Explanation

Step 1: Increase GROUP_CONCAT Maximum Length

set group_concat_max_len = 1000000;

• The GROUP_CONCAT function in MySQL has a default maximum length for the concatenated string it can produce. By setting group_concat_max_len to a large value (1,000,000 in this case), we ensure that the dynamic SQL string generated will not be truncated, even when dealing with numerous stores.

Step 2: Initialize the SQL Variable

set @sql = null;

• Initializes the user-defined variable @sql to NULL. This variable will later store the dynamically constructed SQL statement.

Step 3: Retrieve Store Column Names

with stores as (
    SELECT COLUMN_NAME store 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME='Products' 
      and COLUMN_NAME<>'product_id'
)

• This Common Table Expression (CTE) named stores retrieves all column names from the Products table that represent stores. It excludes the product_id column.

• Explanation:

  • INFORMATION_SCHEMA.COLUMNS: A system table that contains information about all columns in all tables within the database.
  • TABLE_NAME='Products': Filters the columns to only those belonging to the Products table.
  • COLUMN_NAME<>'product_id': Excludes the product_id column since it's not a store column.
  • AS store: Aliases the COLUMN_NAME as store for clarity in subsequent steps.

• Result:

  • A list of store names corresponding to the columns in the Products table.

Step 4: Dynamically Construct SELECT Statements for Each Store

select group_concat(
    concat(
        'select product_id, "', store, '" ', 
        'as store, ', store, ' ', 
        'as price from Products where ', store, ' is not null union'
    ) 
    order by store 
    separator ' '
) into @sql
from stores;

• It constructs a series of SELECT statements for each store, combining them using UNION to create a comprehensive query that unpivots the data.

• Breakdown:

  • concat(...): For each store, creates a SELECT statement that retrieves the product_id, the store name as store, and the corresponding price. It includes a WHERE clause to filter out NULL prices, ensuring only available products are included.
    • Example for store Shop:

      select product_id, "Shop" as store, Shop as price from products where Shop is not null union

  • group_concat(...): Aggregates all the constructed SELECT statements into a single string, separated by spaces.
    • order by store: Ensures that the SELECT statements are ordered lexicographically based on store names.
    • separator ' ': Specifies a space as the separator between concatenated strings.
  • into @sql: Stores the aggregated SELECT statements into the @sql variable for later execution.

• Example Output for the Given Stores (LC_Store, Nozama, Shop, Souq):

  select product_id, "LC_Store" as store, LC_Store as price from products where LC_Store is not null union
  select product_id, "Nozama" as store, Nozama as price from products where Nozama is not null union
  select product_id, "Shop" as store, Shop as price from products where Shop is not null union
  select product_id, "Souq" as store, Souq as price from products where Souq is not null union

Step 5: Remove the Trailing 'UNION'

/*select SUBSTRING(@sql, 1, LENGTH(@sql)-6);*/
set @sql = SUBSTRING(@sql, 1, LENGTH(@sql)-6);

• Explanation:

  • SUBSTRING(@sql, 1, LENGTH(@sql)-6):
    • LENGTH(@sql): Calculates the total length of the concatenated string.
    • -6: Removes the last 6 characters (' union').
    • SUBSTRING: Extracts the substring from the first character up to the length minus 6, effectively trimming the trailing ' union'.

• Result:

  • A properly formatted SQL statement without the trailing ' union', ready for execution.

• Final Dynamic SQL Example:

  select product_id, "LC_Store" as store, LC_Store as price from products where LC_Store is not null union
  select product_id, "Nozama" as store, Nozama as price from products where Nozama is not null union
  select product_id, "Shop" as store, Shop as price from products where Shop is not null union
  select product_id, "Souq" as store, Souq as price from products where Souq is not null

Step 6: Prepare and Execute the Dynamic SQL Statement

prepare stmt from @sql;
execute stmt;

• Explanation:

  • PREPARE stmt FROM @sql;: Takes the dynamic SQL stored in @sql and prepares it as a statement named stmt.
  • EXECUTE stmt;: Runs the prepared statement, generating the unpivoted result set.

• Final Output:

  Executing the prepared dynamic SQL will produce a table in the desired long format. For example:

  +------------+----------+-------+
  | product_id | store    | price |
  +------------+----------+-------+
  | 1          | LC_Store | 100   |
  | 1          | Shop     | 110   |
  | 1          | Souq     | 5000  |
  | 2          | Nozama   | 200   |
  | 2          | Souq     | 190   |
  | 3          | Shop     | 1000  |
  | 3          | Souq     | 1900  |
  +------------+----------+-------+

  • Interpretation:
    • Each product_id appears multiple times, once for each store where it is sold.
    • The price column reflects the product's price in the respective store.
    • Rows where the product is not sold in a store are excluded, as per the WHERE store IS NOT NULL condition in the dynamic SQL.

Pattern: dynamic UNPIVOT (metadata-driven UNION ALL)

Name: turn wide store columns into tall (product_id, store, price) rows by generating SQL from information_schema when the store set is not fixed at authoring time.

Static alternative (when store set is known):

SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2', store2 FROM Products WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3', store3 FROM Products WHERE store3 IS NOT NULL;
-- Postgres 11+ / Oracle / SQL Server also have UNPIVOT or CROSS JOIN LATERAL (VALUES …)

Prefer static UNION ALL in application code when columns rarely change — simpler, plan-cache friendly, no dynamic SQL.

Case-sensitivity of INFORMATION_SCHEMA: matching TABLE_NAME = 'Products' is case-sensitive on some MySQL installs when lower_case_table_names=0 (typical on Linux). If the table was created as products or PRODUCTS, a hard-coded 'Products' returns zero columns and generates empty/invalid SQL. Compare using the actual table name as stored, or normalize with the same casing the server uses (SHOW TABLES, query information_schema.tables without assuming CamelCase).

SQL injection: never concatenate unsanitized user input into dynamic SQL. Table/column names from information_schema are safer than user strings, but if any identifier comes from an API parameter, allow-list it against information_schema before quoting with backticks/`quote_ident`.

When-NOT: dynamic unpivot inside a hot OLTP path on every request — cache the generated statement or redesign to a tall fact table so unpivot is unnecessary.

Drill: Write the static three-store UNION ALL for the sample Products table; list one failure mode if TABLE_NAME casing is wrong.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Dynamic Unpivoting of a Table

Why this exists / the decision it encodes

Wide store-per-column layout is convenient for pivots but wrong for analytics queries. Unpivot restores long form (product_id, store, price). Dynamic SQL exists because store column names vary by testcase — static UNION ALL is simpler when the schema is fixed.

Worked example with numbers or traced SQL/FD

Wide: product_id | Shop | LC | Noi
Long: rows (id,'Shop',price) UNION ALL … WHERE price IS NOT NULL
Dynamic MySQL sketch:
  INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Products' AND COLUMN_NAME<>'product_id'
  GROUP_CONCAT(CONCAT('SELECT product_id, \"',store,'\" AS store, ',store,
                      ' AS price FROM Products WHERE ',store,' IS NOT NULL UNION'))
  strip trailing UNION; PREPARE/EXECUTE
Case: MySQL table names may be case-sensitive depending on OS/lower_case_table_names —
filter TABLE_SCHEMA = DATABASE() too
Security: never concatenate untrusted identifiers; catalog-driven names only
group_concat_max_len must be raised or @sql truncates silently

When NOT / named alternative

Prefer static UNION ALL / CROSS JOIN unnest when ≤ few known stores. Prefer native UNPIVOT (SQL Server/Oracle) or Postgres LATERAL/jsonb when available. Do not dynamic-SQL unpivot for every request in OLTP — materialize long-form or EAV carefully.

Failure mode / ops fingerprint / interview trap

Trap: INFORMATION_SCHEMA without schema filter hits wrong table name in another DB. Ops: SQL injection if store names ever come from user input. Silent truncation of @sql. UNION vs UNION ALL: branches are disjoint by construction — ALL avoids useless dedup.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: wide vs long is a schema-for-query-shape decision. Dynamic SQL is a last resort with catalog safety rails. Prefer UNION ALL over UNION in generated branches.

Hostile-panel drills (with model answers)

Q1. Why dynamic SQL here?
Model answer: Store column names and count vary across testcases (≤30); a fixed SELECT list cannot name unknown columns at procedure author time.

Q2. Static alternative for fixed stores Shop, LC, Noi?
Model answer: SELECT product_id,'Shop' store, Shop price FROM Products WHERE Shop IS NOT NULL UNION ALL SELECT … LC … UNION ALL SELECT … Noi …

Q3. Name two production hazards of this procedure pattern.
Model answer: (1) group_concat truncation without raising max_len. (2) Missing TABLE_SCHEMA filter / case-sensitivity mismatches. (3) Injection if identifiers were not catalog-sourced.