begin time and start time query

Question

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 function in query but its showing error

Answer 1

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

Answer 2

Thanks Mr.Seth for reply