mysql 一列变多列

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(product_info, ':', 1), ':', -1) AS product_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(product_info, ':', -1), ':', 1) AS sales_qty
FROM 
    sales;

DELIMITER //

CREATE PROCEDURE dynamic_pivot()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_product_id VARCHAR(255);
    DECLARE cur_sales_qty INT;
    DECLARE cur_index INT DEFAULT 0;
    DECLARE max_index INT;
    DECLARE pivot_columns VARCHAR(1000) DEFAULT '';
    DECLARE pivot_query VARCHAR(10000) DEFAULT '';

    -- 获取所有唯一的产品ID
    DECLARE cur_product CURSOR FOR SELECT DISTINCT product_id FROM sales;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 构建动态列名
    OPEN cur_product;
    read_loop: LOOP
        FETCH cur_product INTO cur_product_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET cur_index = cur_index + 1;
        SET pivot_columns = CONCAT(pivot_columns, 'MAX(CASE WHEN product_id = ''', cur_product_id, ''' THEN sales_qty END) AS ``', cur_product_id, '''');
        IF cur_index < (SELECT COUNT(DISTINCT product_id) FROM sales) THEN
            SET pivot_columns = CONCAT(pivot_columns, ', ');
        END IF;
    END LOOP;
    CLOSE cur_product;

    -- 构建最终的查询语句
    SET pivot_query = CONCAT('SELECT date, ', pivot_columns, ' FROM (SELECT date, product_id, sales_qty FROM sales) AS src GROUP BY date');

    -- 执行查询并输出结果
    PREPARE stmt FROM pivot_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

CALL dynamic_pivot();