117笔记问答在MySQL中，您可以使用存储过程来实现数据透视
- 首先，创建一个包含数据的示例表：
CREATE TABLE sales_data (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255),
month VARCHAR(255),
sales INT
);
INSERT INTO sales_data (product_name, month, sales)
VALUES ('Product A', 'January', 100),
('Product A', 'February', 200),
('Product B', 'January', 150),
('Product B', 'February', 250);
- 创建一个存储过程来实现数据透视：
DELIMITER //
CREATE PROCEDURE pivot_sales_data()
BEGIN
-- Declare variables
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT DISTINCT month FROM sales_data;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE @query VARCHAR(4096);
DECLARE @month VARCHAR(255);
-- Create a temporary table to store pivoted data
DROP TEMPORARY TABLE IF EXISTS temp_pivot;
CREATE TEMPORARY TABLE temp_pivot (
product_name VARCHAR(255)
);
-- Open the cursor and loop through each month
OPEN cur;
read_loop: LOOP
FETCH cur INTO @month;
IF done THEN
LEAVE read_loop;
END IF;
-- Add a new column for each month in the temporary table
SET @query = CONCAT('ALTER TABLE temp_pivot ADD COLUMN `', @month, '` INT');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
-- Populate the temporary table with pivoted data
SET @query = '
INSERT INTO temp_pivot (product_name, January, February)
SELECT product_name,
SUM(CASE WHEN month = ''January'' THEN sales ELSE 0 END) AS January,
SUM(CASE WHEN month = ''February'' THEN sales ELSE 0 END) AS February
FROM sales_data
GROUP BY product_name';
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Select the pivoted data from the temporary table
SELECT * FROM temp_pivot;
END//
DELIMITER ;
- 调用存储过程以查看透视后的数据：
CALL pivot_sales_data();
这将返回以下结果：
+-------------+---------+----------+ | product_name | January | February | +-------------+---------+----------+ | Product A | 100 | 200 | | Product B | 150 | 250 | +-------------+---------+----------+
请注意，此示例仅适用于具有固定月份列的简单数据透视。对于更复杂的数据透视需求，您可能需要根据您的具体情况修改存储过程。