Mysql一维表转二维表，动态的行转列

背景

想利用Grafana做数据展示，将一维的长表优化成二维数据表格展示。

将一维表转换为二维表，也就是将行转换为列，可以使用MySQL的PIVOT语句来完成。

PIVOT是一种在关系型数据库中将行转换为列的技术。在MySQL中，可以使用PIVOT语句将普通的查询结果转换为一个带有动态列的表格。

PIVOT语句通过将行的值转换为列的值来重新排列数据。在MySQL中，PIVOT语句通常使用聚合函数（如SUM、MAX、MIN等）来对数据进行汇总，以便在转换后的表格中每个单元格只有一个值。

假设条件

假设我们有一个名为“orders”的表，其中存储了订单的信息，包括订单号、商品名称和商品数量：

CREATE TABLE orders (
    order_id INT,
    product_name VARCHAR(50),
    quantity INT
);

现在，我们想将这个一维表转换为二维表，其中每列都代表一个商品名称，每行都代表一个订单，数量为该订单中该商品的数量。

实际两种处理情况

若是要转换成的二维表的表头是已知固定的，则为静态转换；
若是要转换成的二维表的表头是不确定的，根据数据动态增加，则为动态转换。

静态转换

SELECT order_id,
       MAX(CASE WHEN product_name = 'Product A' THEN quantity END) AS 'Product A',
       MAX(CASE WHEN product_name = 'Product B' THEN quantity END) AS 'Product B',
       MAX(CASE WHEN product_name = 'Product C' THEN quantity END) AS 'Product C'
FROM orders
GROUP BY order_id;

或者使用IF判断

SELECT order_id,
	Sum(if(product_name = 'Product A',quantity,0)) AS 'Product A',
	Sum(if(product_name = 'Product B',quantity,0)) AS 'Product B',
	Sum(if(product_name = 'Product C',quantity,0)) AS 'Product C',
FROM orders
GROUP BY order_id;

聚合函数（SUM、MAX、MIN）可以根据需要替换

动态转换

使用动态SQL生成PIVOT语句的具体实现方法可以根据您的具体情况而定，常用以下两种方式：

• 使用存储过程：可以编写一个存储过程，该存储过程接受列名称作为输入参数，并使用动态SQL生成PIVOT语句。存储过程可以将查询结果作为参数返回，并将结果集转换为带有动态列的表格。这种方法的好处是可以将查询逻辑封装在存储过程中，使代码更加模块化和可重用。

• 使用预处理语句：可以编写一个包含动态列名称的字符串，并在MySQL中使用预处理语句来执行动态SQL。这种方法的好处是可以将查询逻辑与数据分离，并且可以减少SQL注入的风险。

存储过程方式

1. 创建一个存储过程，该存储过程接受列名称作为输入参数，并使用动态SQL生成PIVOT语句。以下是一个示例：

DELIMITER $$
CREATE PROCEDURE pivot_sales(IN col_name VARCHAR(50))
BEGIN
    SET @cols = (SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN ', col_name, ' = ''', 
                    REPLACE(val, '''', ''''''), ''' THEN amount END) AS ''', 
                    REPLACE(val, '''', ''''''))) 
                FROM sales
                CROSS JOIN (SELECT DISTINCT val FROM sales WHERE col_name = 'region') r;
    SET @query = CONCAT('SELECT date, ', @cols, ' FROM sales GROUP BY date');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

说明：DEALLOCATE PREPARE语句用于释放由PREPARE语句占用的资源。在MySQL中，PREPARE语句用于准备动态SQL，并将其存储在MySQL服务器的缓存中。当不再需要动态SQL时，可以使用DEALLOCATE PREPARE语句来释放缓存中的语句，以释放资源并减少内存使用。

说明：DELIMITER是MySQL中的一个命令，它用于更改SQL语句的结束符号（通常为分号）。在默认情况下，MySQL使用分号作为SQL语句的结束符号。但是，当在存储过程或函数中使用多条SQL语句时，分号可能会被解释为SQL语句的结束符号，从而导致语法错误。

为了避免这个问题，可以使用DELIMITER命令更改SQL语句的结束符号，例如将结束符号更改为$$。这样，使用分号作为SQL语句的内部结束符号不会与外部SQL语句的结束符号冲突。

在使用DELIMITER命令时，应该首先使用DELIMITER命令设置新的结束符号，然后编写SQL语句，并使用新的结束符号结束SQL语句。最后，应该使用DELIMITER命令将结束符号更改回原始值。

2. 调用存储过程并将列名称作为参数传递。以下是一个示例：

CALL pivot_sales('region');

存储过程中的动态SQL可能会导致SQL注入的风险，因此应该谨慎使用，并确保输入的列名称是可信的。

预处理语句方式（常用）

SET @cols = (SELECT 
				GROUP_CONCAT(DISTINCT 
					CONCAT('MAX(CASE WHEN region = ''', region, ''' THEN sales END) AS ', region)
				) 
			FROM sales);
			
SET @query = CONCAT('SELECT date, ', @cols, ' FROM sales GROUP BY date');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

在这个示例中，我们使用了MySQL的GROUP_CONCAT函数来动态生成列名称，然后使用CONCAT函数将列名称与PIVOT语句组合在一起。最后，我们使用PREPARE语句来准备动态SQL并使用EXECUTE语句来执行动态SQL。