sql - 通过删除 NULL 值重构表

Question

我在 SQL 中有一个如下所示的表:

Customer   Product    1999     2000     2001    2002      2003
Smith      51         NULL     NULL       15      14      NULL
Jones      14           11        7     NULL    NULL      NULL
Jackson    13         NULL     NULL     NULL       3         9

每一年栏下的数字是金额，以美元为单位。每个客户有连续两年的金额，其余年份为零。我想重新构建此表，以便它不再包含大量年份，而只有两列 Amount-Year1 和 Amount-Year2。因此它选择了两个非零年份并将它们以正确的顺序放入这些列中。这将大大减小我的表的大小。

到目前为止，我已经能够重新构建它，以便有一个金额列和一年列，但我随后为每个客户获得多行，不幸的是我不能拥有(由于下游分析)。谁能想出一种方法来获取两个 Amount-Year 列？

我希望决赛 table 看起来像这样:

Customer    Product    Amount_Y1    Amount_Y2
Smith       51         15           14
Jones       14         11            7
Jackson     13          3            9

我不介意丢失有关特定年份的信息，因为我可以从其他来源获得这些信息。实际表中有1999年到2018年所有年份的数据，以后还会有更多年份。

谢谢

Answer 1

幸运的是 UNPIVOT 无论如何都会删除 NULL，因此我们可以使用 UNPIVOT/ROWNUMBER() 来完成此操作， 枢轴:

declare @t table (Customer varchar(15),Product int,[1999] int,
                  [2000] int,[2001] int,[2002] int,[2003] int)
insert into @T(CUstomer,Product,[1999],[2000],[2001],[2002],[2003]) values
('Smith'  ,51,NULL,NULL,  15,  14,NULL),
('Jones'  ,14,  11,   7,NULL,NULL,NULL),
('Jackson',13,NULL,NULL,NULL,   3,   9)

;With Numbered as (
    select
        Customer,Product,Value,
        ROW_NUMBER() OVER (PARTITION BY Customer,Product
                           ORDER BY Year) rn
    from
        @t t
            unpivot
        (Value for Year in ([1999],[2000],[2001],[2002],[2003])) u
)
select
    *
from
    Numbered n
        pivot
    (SUM(Value) for rn in ([1],[2])) w

结果:

Customer        Product     1           2
--------------- ----------- ----------- -----------
Jackson         13          3           9
Jones           14          11          7
Smith           51          15          14