如何在SQL中对含多列的表创建Pivot Table？

Got it, let's tackle this pivot operation for your table containing bdate, th_name, amt, NetSale, GST, and Occupancy columns. First, here's your raw data formatted into a readable table for reference:

Assuming you want to pivot this data to compare metrics across screens per date (a common use case), here are solutions using two popular tools:

1. Python Pandas Solution #

Pandas makes pivoting straightforward with pivot_table. This example groups data by bdate (rows), splits columns by th_name, and aggregates all numeric metrics using sum:

import pandas as pd

# Load your data into a DataFrame
data = [
    ["3/18/18 12:00 AM", "Screen 1", 212540, 169474.1128, 43065.88718, 1752],
    ["3/18/18 12:00 AM", "Screen 2", 242585, 193562.5, 49022.5, 2065],
    ["3/18/18 12:00 AM", "Screen 3", 45840, 36759.26906, 9080.730929, 438],
    ["3/18/18 12:00 AM", "Screen 4", 43470, 34855.73358, 8614.266417, 424],
    ["3/18/18 12:00 AM", "Screen 5", 33600, 26250, 7350, 122],
    ["3/19/18 12:00 AM", "Screen 1", 1755, 1376.059322, 378.940677, 13],
    ["3/19/18 12:00 AM", "Screen 2", 1780, 1490.598516, 289.401481, 20]
]

df = pd.DataFrame(data, columns=["bdate", "th_name", "amt", "NetSale", "GST", "Occupancy"])

# Perform pivot operation
pivot_df = pd.pivot_table(
    df,
    index="bdate",          # Rows will be dates
    columns="th_name",      # Columns will be screen names
    values=["amt", "NetSale", "GST", "Occupancy"],  # Metrics to pivot
    aggfunc="sum",          # Aggregation method (use 'mean' for averages, etc.)
    fill_value=0            # Fill missing values (e.g., no data for a screen on a date) with 0
)

# Optional: Flatten multi-level columns for readability
pivot_df.columns = [f"{col[1]}_{col[0]}" for col in pivot_df.columns]

# Print or save the result
print(pivot_df)

The output will have flat columns like Screen 1_amt, Screen 2_NetSale, making it easy to compare metrics across screens per date.

2. SQL Solution #

If your data is stored in a database, here's how to pivot it with two common approaches:

MySQL/PostgreSQL (Using CASE Statements) #

Since these databases don't have a native PIVOT function, we use CASE to manually split columns:

SELECT
    bdate,
    -- Screen 1 metrics
    SUM(CASE WHEN th_name = 'Screen 1' THEN amt ELSE 0 END) AS 'Screen1_amt',
    SUM(CASE WHEN th_name = 'Screen 1' THEN NetSale ELSE 0 END) AS 'Screen1_NetSale',
    SUM(CASE WHEN th_name = 'Screen 1' THEN GST ELSE 0 END) AS 'Screen1_GST',
    SUM(CASE WHEN th_name = 'Screen 1' THEN Occupancy ELSE 0 END) AS 'Screen1_Occupancy',
    -- Screen 2 metrics
    SUM(CASE WHEN th_name = 'Screen 2' THEN amt ELSE 0 END) AS 'Screen2_amt',
    SUM(CASE WHEN th_name = 'Screen 2' THEN NetSale ELSE 0 END) AS 'Screen2_NetSale',
    SUM(CASE WHEN th_name = 'Screen 2' THEN GST ELSE 0 END) AS 'Screen2_GST',
    SUM(CASE WHEN th_name = 'Screen 2' THEN Occupancy ELSE 0 END) AS 'Screen2_Occupancy',
    -- Add Screen 3, 4, 5 metrics similarly
    SUM(CASE WHEN th_name = 'Screen 3' THEN amt ELSE 0 END) AS 'Screen3_amt',
    SUM(CASE WHEN th_name = 'Screen 3' THEN NetSale ELSE 0 END) AS 'Screen3_NetSale',
    SUM(CASE WHEN th_name = 'Screen 3' THEN GST ELSE 0 END) AS 'Screen3_GST',
    SUM(CASE WHEN th_name = 'Screen 3' THEN Occupancy ELSE 0 END) AS 'Screen3_Occupancy',
    SUM(CASE WHEN th_name = 'Screen 4' THEN amt ELSE 0 END) AS 'Screen4_amt',
    SUM(CASE WHEN th_name = 'Screen 4' THEN NetSale ELSE 0 END) AS 'Screen4_NetSale',
    SUM(CASE WHEN th_name = 'Screen 4' THEN GST ELSE 0 END) AS 'Screen4_GST',
    SUM(CASE WHEN th_name = 'Screen 4' THEN Occupancy ELSE 0 END) AS 'Screen4_Occupancy',
    SUM(CASE WHEN th_name = 'Screen 5' THEN amt ELSE 0 END) AS 'Screen5_amt',
    SUM(CASE WHEN th_name = 'Screen 5' THEN NetSale ELSE 0 END) AS 'Screen5_NetSale',
    SUM(CASE WHEN th_name = 'Screen 5' THEN GST ELSE 0 END) AS 'Screen5_GST',
    SUM(CASE WHEN th_name = 'Screen 5' THEN Occupancy ELSE 0 END) AS 'Screen5_Occupancy'
FROM your_table_name
GROUP BY bdate;

SQL Server (Using Native PIVOT) #

SQL Server has a built-in PIVOT function that simplifies this workflow:

SELECT *
FROM (
    -- First unpivot metrics to a single column
    SELECT 
        bdate,
        CONCAT(th_name, '_', metric) AS pivot_column,
        value
    FROM your_table_name
    UNPIVOT (
        value FOR metric IN (amt, NetSale, GST, Occupancy)
    ) AS unpivot_step
) AS source_data
PIVOT (
    SUM(value)
    FOR pivot_column IN (
        [Screen 1_amt], [Screen 1_NetSale], [Screen 1_GST], [Screen 1_Occupancy],
        [Screen 2_amt], [Screen 2_NetSale], [Screen 2_GST], [Screen 2_Occupancy],
        [Screen 3_amt], [Screen 3_NetSale], [Screen 3_GST], [Screen 3_Occupancy],
        [Screen 4_amt], [Screen 4_NetSale], [Screen 4_GST], [Screen 4_Occupancy],
        [Screen 5_amt], [Screen 5_NetSale], [Screen 5_GST], [Screen 5_Occupancy]
    )
) AS pivot_result;

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