mysql 一列变多列

基础概念

MySQL中，将一列数据转换为多列数据的过程通常称为“列转行”或“透视表”操作。这种操作在数据分析、报表生成等场景中非常常见。通过这种转换，可以将原本存储在单一列中的数据分散到多个列中，以便更直观地展示和分析。

类型与应用场景

静态列转行：在已知列数的情况下，通过SQL语句将一列数据转换为固定数量的多列。适用于数据结构相对稳定的情况。
动态列转行：根据数据本身的特点，动态地将一列数据转换为不同数量的多列。适用于数据结构灵活多变的情况，如日志分析、销售报表等。

遇到的问题及解决方法

问题1：如何实现静态列转行？

解决方法：

假设我们有一个名为sales的表，其中有一个product_info列，存储了产品ID和销售数量的信息，格式为“产品ID:销售数量”。现在我们想将这个列转换为两个独立的列：product_id和sales_qty。

可以使用MySQL的字符串处理函数和正则表达式来实现这一转换。以下是一个示例SQL语句：

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;

参考链接：MySQL字符串处理函数

问题2：如何实现动态列转行？

解决方法：

动态列转行通常需要借助编程语言或存储过程来实现。以下是一个使用MySQL存储过程的示例：

首先，创建一个存储过程来处理动态列转行：

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 ;

然后，调用存储过程来执行动态列转行：