Dynamic Unpivoting of a Table — LeetCode 2253 Python Solution

Table: Products +-------------+---------+ | 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.

+-------------+---------+
| 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.

import duckdb
import pandas as pd

def solution(products: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Products", products)
    return con.execute("""CREATE PROCEDURE UnpivotProducts()
BEGIN
    SET group_concat_max_len = 5000;
    WITH
        t AS (
            SELECT column_name
            FROM information_schema.columns
            WHERE
                table_schema = DATABASE()
                AND table_name = 'Products'
                AND column_name != 'product_id'
        )
    SELECT
        GROUP_CONCAT(
            'SELECT product_id, \'',
            column_name,
            '\' store, ',
            column_name,
            ' price FROM Products WHERE ',
            column_name,
            ' IS NOT NULL' SEPARATOR ' UNION '
        ) INTO @sql from t;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;""").df()