CODEWITHSUNDEEP
sql-server
Niar
Niar

Reputation: 540

How to select different row values in different column

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

Upvotes: 8

Views: 5118

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

One more option with an ranking function 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'

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 3

No Idea For Name
No Idea For Name

Reputation: 11597

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

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'.

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

Upvotes: 3

Related Questions