基于多列值透视SQL表：实现现有输出到期望格式的方案问询

Got it, let's turn that row-based data into a cleaner, pivoted format where each name has its Bid and Ask values side by side. Here are three straightforward methods using SQL, Python, and Excel:

If you're working directly in your database, the approach varies slightly by dialect, but these two methods cover most cases:

Using CASE Statements (Universal across SQL dialects) #

This works in PostgreSQL, MySQL, SQL Server, and more—no fancy operators needed:

SELECT
  Name,
  Date,
  MAX(CASE WHEN Identifier = 'Bid' THEN Value END) AS Bid,
  MAX(CASE WHEN Identifier = 'Ask' THEN Value END) AS Ask
FROM your_table_name
GROUP BY Name, Date;

The MAX() function just collapses the two rows per Name/Date pair into one, since each Identifier has exactly one value per group.

Using PIVOT (SQL Server, Oracle, PostgreSQL 11+) #

If your database supports the PIVOT operator, this is a more concise option:

-- Example for SQL Server
SELECT Name, Date, Bid, Ask
FROM (
  SELECT Name, Identifier, Date, Value
  FROM your_table_name
) AS source_data
PIVOT (
  MAX(Value)
  FOR Identifier IN (Bid, Ask)
) AS pivoted_table;

Pandas makes pivoting data trivial. Here's a step-by-step example:

import pandas as pd

# Load your data (replace this with pd.read_sql or pd.read_csv for your actual data)
raw_data = [
    {"Name": "A", "Identifier": "Bid", "Date": "XX/XX", "Value": 10},
    {"Name": "A", "Identifier": "Ask", "Date": "XX/XX", "Value": 11},
    {"Name": "B", "Identifier": "Bid", "Date": "YY/YY", "Value": 20},
    {"Name": "B", "Identifier": "Ask", "Date": "YY/YY", "Value": 21}
]

df = pd.DataFrame(raw_data)

# Pivot to get Bid/Ask as columns
pivoted_df = df.pivot(
    index=["Name", "Date"],
    columns="Identifier",
    values="Value"
).reset_index()

# Clean up the column header
pivoted_df.columns.name = None

print(pivoted_df)

This will output exactly the side-by-side format you want.

Two easy ways to handle this in Excel:

Method 1: PivotTable (Quick and visual) #

1. Select your entire dataset (including headers).
2. Go to the Insert tab and click PivotTable.
3. In the PivotTable Fields pane:
   • Drag Name and Date to the Rows area.
   • Drag Identifier to the Columns area.
   • Drag Value to the Values area (use Sum or Max—either works since each group has one value).
4. Adjust formatting to match your target layout.

Method 2: INDEX/MATCH (Dynamic, updates with source data) #

If you want a table that auto-updates when your source data changes:

• In cell F2 (Name), enter =UNIQUE(A2:A5) to get distinct names.
• In cell G2 (Date), enter =XLOOKUP(F2, A2:A5, C2:C5) to pull the matching date.
• In cell H2 (Bid), enter =INDEX(D2:D5, MATCH(1, (A2:A5=F2)*(B2:B5="Bid"), 0)) (press Ctrl+Shift+Enter if using older Excel versions).
• In cell I2 (Ask), enter =INDEX(D2:D5, MATCH(1, (A2:A5=F2)*(B2:B5="Ask"), 0)).
• Drag these formulas down to fill all rows.

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