Richie_C


Pivot Table
Hi All
I have a table which keeps the exam room start and end times, i need to pivot the table so it will be broken
down to room and date.
The table looks like this:
Room Start_time End_time
RM1 2005-01-11 09:11:00.000 2005-01-11 10:48:00.000
RM1 2005-01-11 13:32:00.000 2005-01-11 14:51:00.000
RM2 2005-01-11 13:33:00.000 2005-01-11 16:21:00.000
RM2 2005-01-12 09:08:00.000 2005-01-12 13:14:00.000
RM2 2005-01-12 14:17:00.000 2005-01-12 16:26:00.000
I need it to look like this:
Room Start_time1 End_time1 Start_time2 End_time2
RM1 2005-01-11 09:11:00.000 2005-01-11 10:48:00.000 2005-01-11 13:32:00.000 2005-01-11 14:51:00.000
RM2 2005-01-11 13:33:00.000 2005-01-11 16:21:00.000
RM2 2005-01-12 09:08:00.000 2005-01-12 13:14:00.000 2005-01-12 14:17:00.000 2005-01-12 16:26:00.000

Thanks
Rich



Re: HELP!! Pivot table in SQL2k

hunchback


Try:

select

Room,

max(case when rn = 1 then Start_time then null end) as Start_time1,

max(case when rn = 1 then End_time then null end) as End_time1,

max(case when rn = 2 then Start_time then null end) as Start_time2,

max(case when rn = 2 then End_time then null end) as End_time2

from

(

select

a.*,

(

select count(*)

from dbo.t1 as b

where b.room = a.Room and b.Start_titme <= a.Start_time

) as rn

from

dbo.t1

) as t2

group by

Room

go

AMB