sql - SQL Server，将列中的所有值与另一个值的一些值连接起来

Question

我很不擅长解释，所以我会试着让我的例子来做大部分的谈话。假设我有一张这样的桌子：

          dbo.ExampleTable
===================================


  ID     Year     Data1     Data2
====== ======== ========= =========
  12     2016      FOO       BAR
  13     2016      FOO       MAN
  14     2016      SAW       BAR

  20     2017      FOO       BAR
  21     2017      FOO       MAN
  27     2017      SAW       BAR
  29     2017      CHU       CAR

  44     9999      FOO       BAR
  48     9999      FOO       MAN
  51     9999      SAW       BAR
  52     9999      CHU       CAR

一些注意事项：

• ID 是唯一的
• (Year, Data1, Data2) 是唯一的
• “年份”列中的唯一值将是 2016、2017 或 9999

我想从该数据创建一个如下所示的表：

  ID_9999     ID_2016     ID_2017  
=========== =========== ===========
     44          12          20
     48          13          21
     51          14          27
     52         NULL         29

因此，基本上，对于 Year=9999 的 Data1 和 Data2 的每个唯一配对，我想创建一个行，其中包含 Year=9999 的配对的 ID，以及 Year=2016 和 Year= 的配对的 ID 2017 年。此外，如果 2016 或 2017 不包含该数据配对，我希望它们的值为 NULL。

这是我到目前为止的查询：

SELECT      tbl9999.ID       ID_9999,
            tbl2016.ID       ID_2016,
            tbl2017.ID       ID_2017

FROM        dbo.ExampleTable tbl9999

LEFT JOIN   dbo.ExampleTable tbl2016
ON          tbl9999.Data1 = tbl2016.Data1
AND         tbl9999.Data2 = tbl2016.Data2

LEFT JOIN   dbo.ExampleTable tbl2017
ON          tbl9999.Data1 = tbl2017.Data1
AND         tbl9999.Data2 = tbl2017.Data2

WHERE       tbl9999.Year=9999
AND         tbl2016.Year=2016
AND         tbl2017.Year=2017

这似乎工作得很好，但是它会生成一个像这样的表：

  ID_9999     ID_2016     ID_2017  
=========== =========== ===========
     44          12          20
     48          13          21
     51          14          27

*请注意，在上面的示例中，它缺少具有空值的行。有什么方法可以更改我的查询以包含该空值，以便在我的示例中包含它？

如果我遗漏任何信息或需要任何澄清，请告诉我。提前致谢！

编辑：我能够自己找到答案！这是我用来达到我想要的结果的代码：

SELECT      [9999] [ID_9999],
            [2016] [ID_2016],
            [2017] [ID_2017]
FROM        dbo.ExampleTable
PIVOT       (MAX([ID]) FOR [Year] IN ([2016],[2017],[9999])) [x]
ORDER BY    ID_9999

标签： sqlsql-server

Answer 1

您可以通过多种方式执行此操作。条件聚合似乎很简单：

select max(case when year = 2016 then id end) as id_2016,
       max(case when year = 2017 then id end) as id_2017,
       max(case when year = 9999 then id end) as id_9999

from (select t.*, row_number() over (partition by year order by id) as seqnum
      from dbo.ExampleTable t
     ) t
group by seqnum
order by seqnum;