Skip to content
Advertisement

Combine two different rows in single table using same table in SQL Server

Consider a database table holding data like that:

Id UserId IsCheckIn DateTime Image AccountId
30356 60866 1 2020-12-19 12:17:17 b622f3e0806f.jpg 10017
30355 60866 0 2020-12-19 10:52:26 b622f3e0806f.jpg 10017
30354 60866 1 2020-12-19 10:51:02 b622f3e0806f.jpg 10017
30353 60866 0 2020-01-20 09:29:42 1596.jpg 10017

Desired output :

Id UserId IsCheckIn InDateTime InImage AccountId IsCheckOut OutDateTime OutImage
30356 60866 1 2020-12-19 12:17:17 b622f3e0806f.jpg 10017 0 2020-12-19 10:52:26 b622f3e0806f.jpg
30354 60866 1 2020-12-19 10:51:02 b622f3e0806f.jpg 10017 0 2020-01-20 09:29:42 1596.jpg

Please help

Thanks in advance….

Advertisement

Answer

If checkins and checkouts always properly interleave, you can use lead():

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement