SQL表重构：移除NULL值并转换为双年份金额列

First, let's visualize your original table clearly:

Since each customer has exactly two consecutive non-null years with amounts, we can reshape this table into the desired Amount-Year1 and Amount-Year2 columns using a two-step process: unpivot the wide table into rows, then pivot back into paired columns.

SQL Solution (Standard SQL with UNPIVOT) #

This works for SQL Server, Oracle, and other databases that support the UNPIVOT operator:

WITH Unpivoted AS (
    SELECT 
        Customer,
        Product,
        CONCAT(Amount, '-', Year) AS AmountYear,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Year) AS RowNum
    FROM YourTable
    UNPIVOT (
        Amount FOR Year IN ([1999], [2000], [2001], [2002], [2003])
    ) AS unpvt
    WHERE Amount IS NOT NULL
)
SELECT 
    Customer,
    Product,
    MAX(CASE WHEN RowNum = 1 THEN AmountYear END) AS [Amount-Year1],
    MAX(CASE WHEN RowNum = 2 THEN AmountYear END) AS [Amount-Year2]
FROM Unpivoted
GROUP BY Customer, Product;

SQL Solution (MySQL-Compatible, No UNPIVOT) #

MySQL doesn't have UNPIVOT, so we'll use UNION ALL to unpivot manually:

WITH Unpivoted AS (
    SELECT Customer, Product, '1999' AS Year, `1999` AS Amount FROM YourTable WHERE `1999` IS NOT NULL
    UNION ALL
    SELECT Customer, Product, '2000' AS Year, `2000` AS Amount FROM YourTable WHERE `2000` IS NOT NULL
    UNION ALL
    SELECT Customer, Product, '2001' AS Year, `2001` AS Amount FROM YourTable WHERE `2001` IS NOT NULL
    UNION ALL
    SELECT Customer, Product, '2002' AS Year, `2002` AS Amount FROM YourTable WHERE `2002` IS NOT NULL
    UNION ALL
    SELECT Customer, Product, '2003' AS Year, `2003` AS Amount FROM YourTable WHERE `2003` IS NOT NULL
),
Numbered AS (
    SELECT 
        Customer,
        Product,
        CONCAT(Amount, '-', Year) AS AmountYear,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Year) AS RowNum
    FROM Unpivoted
)
SELECT 
    Customer,
    Product,
    MAX(CASE WHEN RowNum = 1 THEN AmountYear END) AS `Amount-Year1`,
    MAX(CASE WHEN RowNum = 2 THEN AmountYear END) AS `Amount-Year2`
FROM Numbered
GROUP BY Customer, Product;

How It Works #

1. Unpivoting: We convert each year column into individual rows, keeping only non-null amount values. This gives us one row per valid (year, amount) pair for each customer.
2. Row Numbering: We add a sequential number (1 and 2) to each customer's rows, ordered by year. This lets us distinguish the first and second consecutive years.
3. Conditional Aggregation: We use MAX(CASE...) to pivot the numbered rows back into two columns, combining the amount and year into the Amount-YearX format.

Expected Output #

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