2253. Dynamic Unpivoting of a Table(目前不会)(No)

SQL架构

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

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

The query result format is in the following example.

Example 1:

Input: 
Products table:
+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1          | 100      | null   | 110  | null |
| 2          | null     | 200    | null | 190  |
| 3          | null     | null   | 1000 | 1900 |
+------------+----------+--------+------+------+
Output: 
+------------+----------+-------+
| product_id | store    | price |
+------------+----------+-------+
| 1          | LC_Store | 100   |
| 1          | Shop     | 110   |
| 2          | Nozama   | 200   |
| 2          | Souq     | 190   |
| 3          | Shop     | 1000  |
| 3          | Souq     | 1900  |
+------------+----------+-------+
Explanation: 
Product 1 is sold in LC_Store and Shop with prices of 100 and 110 respectively.
Product 2 is sold in Nozama and Souq with prices of 200 and 190.
Product 3 is sold in Shop and Souq with prices of 1000 and 1900.

别人的代码:

CREATE PROCEDURE UnpivotProducts()
BEGIN
    DECLARE store_name varchar(100) default '';
    DECLARE done INT;
    DECLARE product_cursor CURSOR FOR     
        SELECT column_name FROM information_schema.columns
        WHERE table_name = 'Products' AND column_name <> 'product_id';
    DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;

    SET @sql = '';

    OPEN product_cursor;
    REPEAT
        FETCH product_cursor INTO store_name;
        SET @sql = concat(@sql, 
        'select product_id, ''', 
        store_name, 
        ''' store,',
        store_name, 
        ' price from Products where ',
        store_name, 
        ' is not null union ');
        UNTIL done
    END REPEAT;
    CLOSE product_cursor;

    SET @sql = substring(@sql, 1, length(@sql) - 7);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

作者：zuihulu
链接：https://leetcode.cn/problems/dynamic-unpivoting-of-a-table/solution/lie-zhuan-hang-by-zuihulu-w8s7/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

CREATE PROCEDURE UnpivotProducts()
BEGIN
	# Write your MySQL query statement below.
    set group_concat_max_len=100000;
    set @sql=null;
    select group_concat(concat('select product_id, ''',column_name,''' store,',column_name,' price from products where ',column_name,' is not null') separator ' union ') into @sql
    from (
        select distinct column_name
        from information_schema.columns
        where table_name='Products' and column_name<>'product_id'
    ) t;

    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;
END