SQL Server多列映射Unpivot：将列转为键值对的技术求助

Got it, let's work through this problem together! You’ve got paired columns like S1/S1Date, S2/S2Date and want to unpivot them into a clean, row-based structure where each sport-date pair gets its own row instead of cluttering up a single record. Let’s start with a clear example to set the context.

Example Context #

Raw Input Data #

Desired Output #

Solutions by Database #

1. SQL Server (Most Flexible with CROSS APPLY) #

For paired columns, CROSS APPLY with a VALUES clause is the cleanest approach—it lets you map each column pair directly to a row:

SELECT 
    t.ID,
    v.Sport,
    v.Date
FROM YourTable t
CROSS APPLY (
    VALUES 
        (t.S1, t.S1Date),
        (t.S2, t.S2Date)
        -- Add more lines here if you have S3/S3Date, S4/S4Date, etc.
) v(Sport, Date)
WHERE v.Sport IS NOT NULL -- Optional: skip rows with no sport value

If you specifically want to use UNPIVOT, you can unpivot each column type separately and join them:

WITH SportsUnpivoted AS (
    SELECT ID, SportCol, Sport
    FROM YourTable
    UNPIVOT (
        Sport FOR SportCol IN (S1, S2)
    ) up
),
DatesUnpivoted AS (
    SELECT ID, DateCol, Date
    FROM YourTable
    UNPIVOT (
        Date FOR DateCol IN (S1Date, S2Date)
    ) up
)
SELECT 
    su.ID,
    su.Sport,
    du.Date
FROM SportsUnpivoted su
JOIN DatesUnpivoted du 
    ON su.ID = du.ID 
    AND su.SportCol = REPLACE(du.DateCol, 'Date', '') -- Match S1 to S1Date
WHERE su.Sport IS NOT NULL

2. PostgreSQL (Using LATERAL Joins or Arrays) #

PostgreSQL doesn’t have UNPIVOT, but CROSS JOIN LATERAL works just like SQL Server’s CROSS APPLY:

SELECT 
    t.ID,
    v.Sport,
    v.Date
FROM YourTable t
CROSS JOIN LATERAL (
    VALUES 
        (t.S1, t.S1Date),
        (t.S2, t.S2Date)
) v(Sport, Date)
WHERE v.Sport IS NOT NULL

Alternatively, you can use array functions for a more concise syntax:

SELECT 
    t.ID,
    unnest(ARRAY[t.S1, t.S2]) AS Sport,
    unnest(ARRAY[t.S1Date, t.S2Date]) AS Date
FROM YourTable t
WHERE unnest(ARRAY[t.S1, t.S2]) IS NOT NULL

3. MySQL (Using UNION ALL) #

MySQL doesn’t support CROSS APPLY or UNPIVOT, so UNION ALL is the standard way to unpivot paired columns:

SELECT ID, S1 AS Sport, S1Date AS Date FROM YourTable WHERE S1 IS NOT NULL
UNION ALL
SELECT ID, S2 AS Sport, S2Date AS Date FROM YourTable WHERE S2 IS NOT NULL
-- Add more UNION ALL blocks for additional pairs like S3/S3Date
ORDER BY ID, Date

Quick Tips #

• If you have more paired columns (e.g., S3/S3Date), just extend the VALUES clause (SQL Server/PostgreSQL) or add another UNION ALL block (MySQL).
• The WHERE clause to exclude NULLs is optional—remove it if you want to keep rows where a sport or date is missing.

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