mysql如何做到转置

CREATE TABLE sales (
    product VARCHAR(50),
    region VARCHAR(50),
    amount INT
);

INSERT INTO sales (product, region, amount) VALUES
('ProductA', 'Region1', 100),
('ProductA', 'Region2', 150),
('ProductB', 'Region1', 200),
('ProductB', 'Region2', 250);

SELECT product,
       MAX(CASE WHEN region = 'Region1' THEN amount END) AS Region1,
       MAX(CASE WHEN region = 'Region2' THEN amount END) AS Region2
FROM sales
GROUP BY product;

import pandas as pd
import mysql.connector

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

# 读取数据
query = "SELECT * FROM sales"
df = pd.read_sql(query, db)

# 转置数据
df_transposed = df.pivot(index='product', columns='region', values='amount')

# 打印转置后的数据
print(df_transposed)