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
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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With