How to select different row values in different column

Question

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

@RaduGheorghiu well, with the given information, that's all i can provide — No Idea For Name
– No Idea For Name, Commented Dec 15, 2014 at 7:24
@NoIdeaForName If you're trying to help the OP out, just ask for more details. And if you do intend to troll at least make sure your query doesn't return a syntax error. — Radu Gheorghiu
– Radu Gheorghiu, Commented Dec 15, 2014 at 7:26
@NoIdeaForName I'm running your query and checking. Actually i'm joining some more tables also so its returning some duplicate rows.Any way thanks for the answer — Niar
– Niar, Commented Dec 15, 2014 at 8:00

Pரதீப் · Accepted Answer · 2014-12-15 08:18:06Z

3

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

edited Dec 15, 2014 at 8:18

answered Dec 15, 2014 at 7:30

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Niar Over a year ago

I can't use group by because my actual table contains many rows with same ID and i want each ID, not in group.

Pரதீப் Over a year ago

@Rain Then add sample data and expected output accordingly!!

Niar Over a year ago

Got it. This gave me the correct answer.Although i need to tweak it a little for my purpose.Thanks

No Idea For Name · Accepted Answer · 2014-12-15 07:29:38Z

3

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

answered Dec 15, 2014 at 7:29

No Idea For Name

11.6k11 gold badges49 silver badges73 bronze badges

Comments

Oleksandr Fedorenko · Accepted Answer · 2014-12-15 16:22:38Z

0

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'

answered Dec 15, 2014 at 16:22

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Collectives™ on Stack Overflow

How to select different row values in different column

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related