begin time and start time query

I have following data and using SQL Server 2005

UserID  UserName  LogTime LogDate 
1       S         9:00    21/5/2010 
1       S         10:00   21/5/2010 
1       S         11:00   21/5/2010 
1       S         12:00   21/5/2010 
1       S         14:00   21/5/2010
1       S         17:00   21/5/2010 

Need Output as:-

1     S      21/5/2010 9:00  21/5/2010 10:00 
1     S      21/5/2010 11:00 21/5/2010 12:00 
1     S      21/5/2010 14:00 21/5/2010 17:00 

I had used ROW_NUMBER fu开发者_如何学Pythonnction in query but its showing error

I'm wondering whether there is more to the problem than you have stated. Going strictly by the information you have given, the following example seems to work.

DECLARE @YourTable TABLE(UserId int, UserName varchar(10), LogTime time, LogDate date)

insert @YourTable values(1,'S','9:00','5/21/2010')
insert @YourTable values(1,'S','10:00','5/21/2010')
insert @YourTable values(1,'S','11:00','5/21/2010')
insert @YourTable values(1,'S','12:00','5/21/2010')
insert @YourTable values(1,'S','14:00','5/21/2010')
insert @YourTable values(1,'S','17:00','5/21/2010');

WITH 
    [TableWithRowId] as
    (SELECT ROW_NUMBER() OVER(ORDER BY UserId,LogDate,LogTime) RowId, * FROM @YourTable),
    [OddRows] as 
    (SELECT * FROM [TableWithRowId] WHERE rowid % 2 = 1),
    [EvenRows] as
    (SELECT *, RowId-1 As OddRowId FROM [TableWithRowId] WHERE rowid % 2 = 0)
SELECT 
    [OddRows].UserId,
    [OddRows].UserName,
    [OddRows].LogDate,
    [OddRows].LogTime,
    [EvenRows].LogDate,
    [EvenRows].LogTime 
FROM
    [OddRows] LEFT JOIN [EvenRows]
    ON [OddRows].RowId = [EvenRows].OddRowId