如何将memsql数据行转置为列

SELECT
    id,
    MAX(CASE WHEN product = 'A' THEN sales END) AS A,
    MAX(CASE WHEN product = 'B' THEN sales END) AS B,
    MAX(CASE WHEN product = 'C' THEN sales END) AS C
FROM
    sales
GROUP BY
    id;

SELECT DISTINCT product FROM sales;

import pymysql  # 连接到数据库 connection = pymysql.connect(     host='your_host',     user='your_user',     password='your_password',     database='your_database' )  cursor = connection.cursor()  # 获取所有唯一的产品名称 cursor.execute("SELECT DISTINCT product FROM sales") products = cursor.fetchall()  # 生成动态SQL查询 select_clause = "id" for product in products:     select_clause += f", MAX(CASE WHEN product = '{product[0]}' THEN sales END) AS {product[0]}"  query = f"SELECT {select_clause} FROM sales GROUP BY id"  # 执行查询 cursor.execute(query) result = cursor.fetchall()  # 打印结果 for row in result:     print(row)  # 关闭连接 cursor.close() connection.close()
 

DELIMITER //

CREATE PROCEDURE PivotSales()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE product_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT DISTINCT product FROM sales;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET @select_clause = 'id';
    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO product_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @select_clause = CONCAT(@select_clause, ', MAX(CASE WHEN product = ''', product_name, ''' THEN sales END) AS ', product_name);
    END LOOP;

    CLOSE cur;

    SET @query = CONCAT('SELECT ', @select_clause, ' FROM sales GROUP BY id');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用存储过程
CALL PivotSales();