Crosstab/Pivot without grouping

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 2000 Forums

SQL Server Development (2000)

Crosstab/Pivot without grouping

Author

Topic

qalo
Starting Member

3 Posts

Posted - 2006-10-31 : 12:39:42

greetings all,
i'm trying to crosstab or pivot this data with the following columns eg,

date, datetime, eventype, eventid
20061030, 2006-10-30 08:37:35, IN, 20342624
20061030, 2006-10-30 08:37:51, OUT, 20342703
20061030, 2006-10-30 08:38:13, IN, 20342827
20061030, 2006-10-30 18:19:30, OUT, 20479241

date, timein, timeout
20061030, 08:37:35, 08:37:51
20061030, 08:38:13, 18:19:30

please help...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 13:39:38

[code]-- prepare test data
declare @test table ([date] int, [datetime] datetime, eventype varchar(3), eventid int)

insert @test
select 20061030, '2006-10-30 08:37:35', 'IN', 20342624 union all
select 20061030, '2006-10-30 08:37:51', 'OUT', 20342703 union all
select 20061030, '2006-10-30 08:38:13', 'IN', 20342827 union all
select 20061030, '2006-10-30 18:19:30', 'OUT', 20479241

select * from @test

-- do the work
select [in].[date],
[in].[datetime] timein,
[out].[datetime] timeout
from (
select t.[date],
t.[datetime],
(select min(q.eventid) from @test q where q.eventype = 'out' and q.eventid > t.eventid) eventid
from @test t
where t.eventype = 'in'
) [in]
inner join @test [out] on [out].eventid = [in].eventid
where [in].[datetime] <= [out].[datetime]
order by 1,
2[/code]
Peter Larsson
Helsingborg, Sweden