Dynamic Pivoting of a Table — LeetCode 2252 Python Solution

Table: Products +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | store | varchar | | price | int | +-------------+---------+ (product_id, store) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the price of product_id in store.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store       | varchar |
| price       | int     |
+-------------+---------+
(product_id, store) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of product_id in store.
There will be at most 30 different stores in the table.
price is the price of the product at this store.

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 PivotProducts()
BEGIN
	SET group_concat_max_len = 5000;
    SELECT GROUP_CONCAT(DISTINCT 'MAX(CASE WHEN store = \'',
               store,
               '\' THEN price ELSE NULL END) AS ',
               store
               ORDER BY store) INTO @sql
    FROM Products;
    SET @sql =  CONCAT('SELECT product_id, ',
                    @sql,
                    ' FROM Products GROUP BY product_id');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END""").df()