使用SQL实现长表转宽表：Pivot函数替代方案求助

Got it, let's work through this problem step by step. SQLite doesn't have a native dynamic pivot function, and writing MAX(CASE WHEN...) for hundreds/thousands of SKUs manually is totally impractical. The solution is to use Python (since you're already using SQLAlchemy) to dynamically generate the necessary SQL clauses based on the unique SKUs in your database. Here's how to pull it off:

Step 1: Fetch All Unique SKUs First #

First, we need to grab every distinct SKU from your table—this will be the foundation for our dynamic columns. Using SQLAlchemy, run a quick query to get these:

from sqlalchemy import create_engine, text

# Initialize your SQLite engine (update the DB path to match yours)
engine = create_engine("sqlite:///your_database_file.db")

# Fetch all unique SKUs from your long-format table
with engine.connect() as conn:
    sku_results = conn.execute(text("SELECT DISTINCT sku FROM your_long_format_table")).fetchall()
    # Convert the result tuples to a flat list of SKU strings
    unique_skus = [row[0] for row in sku_results]

Note: Replace your_long_format_table with your actual table name, and sku with the column name holding your SKU values.

Step 2: Dynamically Generate the CASE WHEN Clauses #

Now we'll build the MAX(CASE WHEN...) segments for each SKU automatically—no manual typing required:

# Replace `your_value_column` with the column holding the value you want to pivot (e.g., quantity, price)
case_clauses = [
    f"MAX(CASE WHEN sku = '{sku.replace(\"'\", \"''\")}' THEN your_value_column END) AS `{sku}`"
    for sku in unique_skus
]

• sku.replace("'", "''") handles SKUs with single quotes to avoid SQL syntax errors.
• Wrapping the SKU in backticks (`) ensures column names with spaces or special characters work correctly in SQLite.

Step 3: Build and Execute the Final Pivot Query #

Assemble the full SQL query and run it with SQLAlchemy:

# Replace `your_group_column` with the column you want to group by (e.g., order_id, transaction_date)
pivot_sql = f"""
SELECT
    your_group_column,
    {', '.join(case_clauses)}
FROM your_long_format_table
GROUP BY your_group_column
"""

# Execute the query and fetch the wide-format results
with engine.connect() as conn:
    pivot_results = conn.execute(text(pivot_sql)).fetchall()
    
    # Optional: Convert to a Pandas DataFrame for easier analysis
    import pandas as pd
    df = pd.DataFrame(pivot_results, columns=["your_group_column"] + unique_skus)

Why This Works Better Than Pandas Pivot #

• Memory Efficiency: Unlike Pandas' pivot() which loads the entire long-format dataset into memory first, this does the aggregation directly in SQLite, returning only the compact wide-format results.
• Scalability: Handles hundreds or thousands of SKUs effortlessly—Python takes care of generating all the necessary SQL code.

Edge Cases to Keep in Mind #

• If you have extremely large numbers of SKUs (10k+), SQLite might hit a query length limit. In that case, you could split the SKUs into batches and combine results later, but this is rare for most practical use cases.
• Double-check your grouping column (your_group_column)—this will form the rows of your wide-format table, so it needs to be the correct identifier for each row (e.g., order number, date).

内容的提问来源于stack exchange，提问作者GK89