CODEWITHSUNDEEP
sqlsql-server
roi guber
roi guber

Reputation: 21

How to combine 2 rows with different values

I have the following table:

id  date1     score1     date2     score2
1   today     80         today     88
1   yesterday 90         yesterday 92

The problem is that i need to Combine 2 rows into one row while in each column will be selected the highlighted values, on date1 the newest date follows matching score 1, the exact opposite will be done to date2 and score 2.

Upvotes: 0

Views: 194

Answers (2)

SevincQurbanova
SevincQurbanova

Reputation: 366

select t1.date1,t1.score1,t2.date2,t2.score2
from yourTable t1
cross join yourTable t2 
where t1.date1 like 'today' and t2.date2='yesterday'

This works if you store date1 and date2 as strings 'today' and 'yesterday'

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

If I understand clear,Is that your expect?

SELECT  t1.ID,t1.DATE1,t1.score1,t2.DATE2,t2.score2
FROM T t1 
INNER JOIN T t2 ON t1.id = t2.id AND DATEADD(DAY,1,t2.DATE2) = t1.DATE1

SQLFiddle

OR

In your question you might be like this query.

SELECT  t1.ID,t1.DATE1,t1.score1,t2.DATE2,t2.score2
FROM T t1 
INNER JOIN T t2 ON t1.id = t2.id 
WHERE t1.date2 = 'today' and t2.date2 = 'yesterday'

Upvotes: 0

Related Questions