基于Member ID与Create Date拼接分组的多行转列需求

Got it, let's work through this data transformation task. You need to group rows by the combined key of Member ID and Create Date, then flatten multiple Answer values from the same group into separate columns (Answer and Answer 2). Let's break down solutions for both SQL and Python (Pandas), since those are the most common tools for this kind of work.

Source Data #

Target Output #

Solution 1: SQL (Works with most databases like PostgreSQL, MySQL, SQL Server) #

We'll use window functions to assign a sequence number to each Answer in the same group, then use conditional aggregation to pivot those values into separate columns:

WITH ranked_answers AS (
    SELECT 
        "Member ID",
        "Create Date",
        "Answer",
        -- Assign a unique number to each Answer in the same (Member ID, Create Date) group
        ROW_NUMBER() OVER (PARTITION BY "Member ID", "Create Date" ORDER BY "Row") AS answer_seq
    FROM your_source_table
)
SELECT 
    -- Generate a new Row number for the final output
    ROW_NUMBER() OVER (ORDER BY "Member ID", "Create Date") AS "Row",
    "Member ID",
    "Create Date",
    -- Grab the first Answer in the group
    MAX(CASE WHEN answer_seq = 1 THEN "Answer" END) AS "Answer",
    -- Grab the second Answer (if exists)
    MAX(CASE WHEN answer_seq = 2 THEN "Answer" END) AS "Answer 2"
FROM ranked_answers
GROUP BY "Member ID", "Create Date"
ORDER BY "Member ID", "Create Date";

How this works: #

1. The ranked_answers CTE adds an answer_seq column that numbers each Answer in the group starting from 1, ordered by the original Row number.
2. The main query groups by Member ID and Create Date, then uses MAX(CASE...) to pull the first and second Answer values into their own columns.
3. Finally, we generate a new Row number for the transformed output.

Solution 2: Python with Pandas #

If you're working with data in a Python environment, Pandas makes this pivot operation straightforward:

import pandas as pd

# Load your source data into a DataFrame
df = pd.DataFrame({
    "Row": [1, 2, 3, 4, 5, 6],
    "Member ID": [1234, 1234, 1234, 7778, 7781, 7778],
    "Create Date": [20170101, 20170101, 20170201, 20180301, 20180201, 20180301],
    "Answer": ["20170101", "1", "20170201", "1", "2080201", "20180301"]
})

# Assign a sequence number to each Answer in the same group
df["answer_seq"] = df.groupby(["Member ID", "Create Date"])["Row"].rank(method="first").astype(int)

# Pivot the DataFrame to turn answer_seq into columns
pivoted_df = df.pivot(
    index=["Member ID", "Create Date"],
    columns="answer_seq",
    values="Answer"
).reset_index()

# Rename columns to match your target output
pivoted_df.columns = ["Member ID", "Create Date", "Answer", "Answer 2"]

# Add the Row column
pivoted_df["Row"] = pivoted_df.index + 1

# Reorder columns to match the target layout
pivoted_df = pivoted_df[["Row", "Member ID", "Create Date", "Answer", "Answer 2"]]

# Show the result
print(pivoted_df)

How this works: #

1. We first add an answer_seq column to number each Answer in its group, using the original Row order.
2. The pivot method converts the sequence numbers into column headers, placing each Answer value in the correct column.
3. Finally, we clean up column names, add the new Row number, and reorder columns to match your target table.

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