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

I have a table like.

我有一张桌子。

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

And i want result like where ADate is the Date where Value is A and TDate is the Date where value is 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

3 个解决方案

#1

Use conditional Aggregate. Try this

使用条件聚合。尝试这个

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

Update : To get rows with same 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

#2

you can use a WHERE statement and a JOIN statement to get:

您可以使用WHERE语句和JOIN语句来获取：

SELECT a.id, a.date AS ADate, b.date AS TDate
FROM table a
JOIN table b on a.id = b.id and b.value = 'T'
WHERE a.value = 'A'

the WHERE statement make sure ADate comes from a row where value='A'.

WHERE语句确保ADate来自value ='A'的行。

the JOIN statement make sure TDate comes from a row where value='T'.

JOIN语句确保TDate来自value ='T'的行。

#3

One more option with an ranking function ROW_NUMBER

排名函数ROW_NUMBER的另一个选项

;WITH cte AS
 (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY [date]) AS rn
  FROM dbo.test103 t  
  )
  SELECT c.id, c.[date] AS ADate, c2.[date] AS TDate
  FROM cte c LEFT JOIN cte c2 ON c.Id = c2.Id AND c.rn = c2.rn - 1 AND c.Value != c2.Value
  WHERE c.value = 'A'

#1