技术需求：将包含Start Date与持续时长（或End Date）的宽表转换为按日展开的长表

Got it, let's walk through how to convert your wide table (with start dates and periods) into a long table where every day in the date range gets its own row, repeating the other fields. I'll cover three common tools you might be using:

1. Excel (Power Query) #

Power Query makes this super straightforward without writing any code:

• Select your data range, go to the Data tab, and click From Table/Range to import into the Power Query Editor.
• Add a custom column: Go to Add Column > Custom Column, then paste this formula (it creates a list of dates from the start date through the end date, inclusive):

  = List.Dates([Start Date], [Period (day)] + 1, #duration(1, 0, 0, 0))
  

  The +1 ensures we include both the start date and all days in the period (since 5 days from 01/01/2022 goes up to 06/01/2022, which is 6 total days).
• Click the expand icon (the little arrow) on the header of your new custom column, then select Expand to New Rows.
• Rename the expanded column to Date, then delete the original Start Date and Period (day) columns.
• Go to Home > Close & Load to bring the transformed data back into Excel.

2. Python (Pandas) #

If you prefer coding, Pandas has all the tools to handle this efficiently:

import pandas as pd

# Load your data (replace this with your actual data source)
df = pd.DataFrame({
    "Name": ["Project 1", "Project 2"],
    "Info 1": ["Test 1", "Test 2"],
    "Start Date": ["01/01/2022", "05/01/2022"],
    "Period (day)": [5, 2]
})

# Convert Start Date to datetime format
df["Start Date"] = pd.to_datetime(df["Start Date"], format="%d/%m/%Y")

# Create a column with the full date range for each row
df["Date"] = df.apply(
    lambda row: pd.date_range(
        start=row["Start Date"], 
        periods=row["Period (day)"] + 1,  # +1 to include the end date
        freq="D"
    ),
    axis=1
)

# Explode the date range into individual rows
df = df.explode("Date").reset_index(drop=True)

# Clean up: remove old columns and format dates back to your original style
df = df.drop(columns=["Start Date", "Period (day)"])
df["Date"] = df["Date"].dt.strftime("%d/%m/%Y")

# Check the result
print(df)

3. SQL (MySQL Example) #

If your data is in a database, you can use a recursive CTE to generate the date ranges:

WITH date_bounds AS (
    -- First, calculate the start and end dates for each row
    SELECT 
        Name,
        `Info 1`,
        STR_TO_DATE(`Start Date`, '%d/%m/%Y') AS start_date,
        STR_TO_DATE(`Start Date`, '%d/%m/%Y') + INTERVAL `Period (day)` DAY AS end_date
    FROM your_table  -- Replace with your actual table name
),
recursive_date_list AS (
    -- Start with the initial start dates
    SELECT Name, `Info 1`, start_date AS date
    FROM date_bounds
    UNION ALL
    -- Recursively add each subsequent day until we reach the end date
    SELECT db.Name, db.`Info 1`, rdl.date + INTERVAL 1 DAY
    FROM date_bounds db
    JOIN recursive_date_list rdl 
        ON db.Name = rdl.Name AND db.`Info 1` = rdl.`Info 1`
    WHERE rdl.date < db.end_date
)
-- Final output with formatted dates
SELECT 
    Name,
    `Info 1`,
    DATE_FORMAT(date, '%d/%m/%Y') AS Date
FROM recursive_date_list
-- Optional: order by name and date if needed
ORDER BY Name, Date;

Note: For other SQL dialects (like PostgreSQL or SQL Server), the syntax for date functions will be slightly different, but the recursive CTE approach stays similar.

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