+-------------+---------+
| 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.
Important note: This problem targets those who have a good experience with SQL. If you are a beginner, we recommend that you skip it for now.
Implement the procedure PivotProducts to reorganize the Products table so that each row has the id of one product and its price in each store. The price should be null if the product is not sold in a store. The columns of the table should contain each store and they should be sorted in lexicographical order.
The procedure should return the table after reorganizing it.
Input:
Products table:+------------+----------+-------+| product_id | store | price |+------------+----------+-------+|1| Shop |110||1| LC_Store |100||2| Nozama |200||2| Souq |190||3| Shop |1000||3| Souq |1900|+------------+----------+-------+Output:
+------------+----------+--------+------+------+| product_id | LC_Store | Nozama | Shop | Souq |+------------+----------+--------+------+------+|1|100|null|110|null||2|null|200|null|190||3|null|null|1000|1900|+------------+----------+--------+------+------+Explanation:
We have 4 stores: Shop, LC_Store, Nozama, and Souq. We first order them lexicographically to be: LC_Store, Nozama, Shop, and Souq.Now,for product 1, the price in LC_Store is100 and in Shop is110. For the other two stores, the product is not sold so we set the price as null.Similarly, product 2 has a price of 200in Nozama and 190in Souq. It is not sold in the other two stores.For product 3, the price is1000in Shop and 1900in Souq. It is not sold in the other two stores.
To pivot the Products table dynamically (with an unknown number of stores), we need to generate the column list and the pivot query at runtime. This is typically done using dynamic SQL in SQL Server, MySQL, or PostgreSQL. The idea is to aggregate prices for each product_id, with each store as a column, and prices as values, filling null where not available.
Get the list of unique stores, sorted lexicographically.
Build a dynamic SQL query that selects product_id and, for each store, uses an aggregate function (e.g., MAX(CASE WHEN store = ‘store_name’ THEN price END)) as a column.
SETSESSION group_concat_max_len =1000000;
SET@sql=NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT(
'MAX(CASE WHEN store = ''', store, ''' THEN price END) AS `', store, '`')
ORDERBY store
) INTO@cols
FROM Products;
SET@sql= CONCAT('SELECT product_id, ', @cols, ' FROM Products GROUP BY product_id');
PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
1
2
3
4
5
6
7
8
9
10
11
12
13
DO$$DECLARE col_list text;
dyn_sql text;
BEGINSELECT string_agg(
format('MAX(CASE WHEN store = %L THEN price END) AS "%s"', store, store), ', 'ORDERBY store
) INTO col_list
FROM (SELECTDISTINCT store FROM Products) s;
dyn_sql := format('SELECT product_id, %s FROM Products GROUP BY product_id', col_list);
EXECUTE dyn_sql;
END$$;
1
2
3
4
5
6
7
import pandas as pd
defpivot_products(products: pd.DataFrame) -> pd.DataFrame:
df = products.pivot(index='product_id', columns='store', values='price')
df = df.reset_index()
df = df.reindex(columns=['product_id'] + sorted([c for c in df.columns if c !='product_id']))
return df