SQL多列透视需求：将Month设为列展示Case1/Case2数据

Got it, let's figure out how to get that pivot setup right for your data! From what you described—turning the Month column into headers and placing your Case1/Case2 values under their corresponding months—I’ll walk you through solutions for two common tools: Excel and Python’s Pandas library, since you didn’t specify which one you’re using.

Let’s assume your raw data looks like this (each row holds a month’s values for Case1 and Case2):

Here’s how to pivot it to your desired format:

1. Unpivot the data first (this turns your wide columns into a long format, which makes pivoting easier):
   • Select your entire data range (including headers)
   • Go to the Data tab → click From Table/Range (for Excel 2016+). Check "My table has headers" in the pop-up, then hit OK to open the Power Query Editor.
   • In Power Query, select the Month column, right-click it, and choose Unpivot Other Columns. Your data will now have three columns: Month, Attribute (this will be Case1/Case2), and Value.
   • Click Close & Load to export this reshaped data to a new worksheet.
2. Build the final pivot table:
   • Select the unpivoted data range you just created.
   • Go back to Data → PivotTable, choose where you want to place the table, and click OK.
   • In the PivotTable Fields panel:
     • Drag Attribute to the Rows area
     • Drag Month to the Columns area
     • Drag Value to the Values area
   • Tweak the formatting if needed (like renaming the "Attribute" header to "Case Type" or leaving it blank) and you’ll have your desired result!

If you don’t have Power Query (older Excel versions), you can use a combination of INDEX/MATCH formulas, but Power Query is way more efficient for this kind of reshaping.

If you’re using Python to handle your data, Pandas has straightforward functions to get this done. Let’s start with a sample DataFrame matching your structure:

import pandas as pd

# Sample raw data
df = pd.DataFrame({
    'Month': ['Jan', 'Feb', 'Mar'],
    'Case1': [10, 15, 8],
    'Case2': [20, 25, 18]
})

Method 1: Melt + Pivot (flexible for complex data) #

First, we’ll "unmelt" the Case1/Case2 columns into a long format, then pivot the Month values into columns:

# Reshape to long format: Month, Case, Value
melted_df = df.melt(id_vars='Month', var_name='Case', value_name='Value')
# Pivot to get Case as rows, Month as columns
pivoted_df = melted_df.pivot(index='Case', columns='Month', values='Value').reset_index()
# Clean up the column headers
pivoted_df.columns.name = None

print(pivoted_df)

This will output exactly what you want:

Case  Jan  Feb  Mar
0  Case1   10   15    8
1  Case2   20   25   18

Method 2: Direct Transpose (simple for unique months) #

If your Month column has no duplicate entries, you can skip the melt step and just transpose the data:

# Set Month as the index, transpose, then reset to clean up
transposed_df = df.set_index('Month').transpose().reset_index()
transposed_df.columns.name = None
transposed_df.rename(columns={'index': 'Case'}, inplace=True)

print(transposed_df)

This gives the same result and is quicker for simple datasets.

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