Merge two rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merge two rows

Author

Topic

vss
Starting Member

1 Post

Posted - 2011-05-13 : 18:53:09

I have several SQL record in a table that I need to pair two by two.

For eg. I have the folwing data...

ID TYPE DATE_TIME
------------------------------
1 IN 2011-01-01 09:00
1 OUT 2011-01-01 12:00
1 IN 2011-01-01 13:00
1 OUT 2011-01-01 17:00
2 IN 2011-01-01 09:00
2 OUT 2011-01-01 12:00
2 IN 2011-01-01 13:00
2 OUT 2011-01-01 17:00

I need to output....

ID DATE_TIME_IN DATE_TIPE_OUT
------------------------------------------
1 2011-01-01 09:00 2011-01-01 12:00
1 2011-01-01 13:00 2011-01-01 17:00
2 2011-01-01 09:00 2011-01-01 12:00
2 2011-01-01 13:00 2011-01-01 17:00

Any Ideas how to do this with just SQL commands.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 19:05:18

[code];with cte as
( select *,row_number() over(partition by id order by date_time) as rownum
from YourTable
)
select
a.id,
a.date_time as date_time_in,
b.date_time as date_time_out
from
cte a
inner join cte b on
a.id = b.id and a.rownum+1 = b.rownum
where
a.[type] = 'IN'
and b.[type] = 'OUT'
order by
a.id,
a.rownum[/code]This assumes that the data is always "clean" - i.e., each "IN" is paired with a corresponding "OUT" and that for a given ID, they are always alternating as IN,OUT,IN,OUT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources