sql-server - 如何在不同的列中选择不同的行值

Question

我有一张像这样的 table 。

ID  Date         Value
1   12/12/2014   A
1   24/12/2014   T
2   13/12/2014   A
2   23/12/2014   T
3   12/03/2014   A
3   12/04/2014   T
4   12/12/2014   T
5   12/04/2014   T

我想要这样的结果，其中 ADate 是值是 A 的日期，TDate 是值是 T 的日期

ID    ADate            TDate
1     12/12/2014      24/12/2014
2     13/12/2014      23/12/2014
3     12/03/2014      12/04/2014
4         -           12/12/2014
5         -           12/04/2014

Answer 1

使用条件聚合。试试这个

SELECT id,
       Min(CASE
             WHEN value = 'A' THEN [Date]
           END) Adate,
       Max(CASE
             WHEN value = 'T' THEN [Date]
           END) Tdate
FROM   Tablename
GROUP  BY id 

更新:获取具有相同 ID 的行

DECLARE @cnt INT

SELECT TOP 1 @cnt = Count(1) / 2
FROM   #test
GROUP  BY id
ORDER  BY Count(1) / 2 DESC

SELECT id,
       Min(CASE
             WHEN value = 'A' THEN [Date]
           END) Adate,
       Max(CASE
             WHEN value = 'T' THEN [Date]
           END) Tdate
FROM   (SELECT Row_number()
                 OVER (
                   partition BY id, value
                   ORDER BY date)%@cnt rn,
               *
        FROM   #test) a
GROUP  BY id,rn