SQL 合并行

Question

我有一张包含三列的表格：ID、日期、组。每个唯一 ID（其中组 = 1 或组 = 2）有两行。如何将它们组合成一张表格，每行一个 ID，日期值按两列排序？

我有：

ID      Date        Group
123456  12/7/2016   1
123456  12/3/2016   2
789654  11/8/2016   1
789654  12/1/2016   2

我想：

ID      Date1       Date2
123456  12/7/2016   12/3/2016
789654  11/8/2016   12/1/2016

如果这有关系的话，这适用于 Sybase 12。

Answer 1

SELECT DISTINCT 
    s.ID,
    s1.[Date] AS Date1,
    s2.[Date] AS Date2
FROM @SomeTable s
LEFT JOIN @SomeTable s1 ON s.ID = s1.ID AND s1.[Group] = 1
LEFT JOIN @SomeTable s2 ON s.ID = s2.ID AND s2.[Group] = 2

或者

SELECT DISTINCT
    s.ID,
    (SELECT [Date] FROM @SomeTable s1 WHERE s1.ID = s.ID AND s1.[Group] = 1) AS Date1,
    (SELECT [Date] FROM @SomeTable s2 WHERE s2.ID = s.ID AND s2.[Group] = 2) AS Date2
FROM @SomeTable s

是我首先想到的两种方法。这两种方法都假设每个 ID/Group 组合只有一个记录。

Answer 2

您也可以仅使用“from”子句中的两个表来完成此操作：

select a.ID, a.Date as Date1, b.Date as Date2
from mytable a, mytable b
where a.ID=B.ID
and a.Group=1
and b.Group=2