Fast query for end user report

Question

In project users can reserve rooms for events. In one event we can have many reservations (tblEventTimePeriod) with many rooms (tblEventTimePeriodRoom) I have DB structure like this. I removed unnecessary columns to simplify the example,

tblEvent (ID, Name) 
tblEventTimePeriod (ID, EventId)
tblEventTimePeriodRoom (ID, EventTimePeriodId, RoomId)

Relationships between tables:

tblEvent to tblEventTimePeriod -> One to many
tblEventTimePeriod to tblEventTimePeriodRoom -> many to many

For this example, RoomId can take values from 1 to 5. In real project it has 40 different values (Keys in other table) which I have to show as columns in report.

My problem is - how to build fast query to get result as below:

EventId | EventName | RoomId_1 | RoomId_2 | RoomId_3 | RoomId_4 | RoomId_5

RoomId_X - meens than Event has reserved RoomId = X. It doesn't matters which tblEventTimePeriod has this reservation.

Actual solution is using scalar UDF (User Defined Function) to get this information. At the beginning it was fine, but now executation time is not acceptable. In fact, For each row (tblEvent) it execute subquery to tblEventTimePeriodRoom joined to tblEventTimePeriod to check row exists. When report has 40 columns.... No comment :)

I will be grateful for any hints! I'm using SQL Server 2008 R2.

Example data:

tblEvent:
----------
Id | Name
----------
1 | Event1
2 | Event2
3 | Event3

tblEventTimePeriod:
------------
Id | EventId
------------
12 | 1
13 | 2
14 | 2
15 | 3

tblEventTimePeriodRoom
-------------------------------
Id | EventTimePeriodId | RoomId
-------------------------------
110 | 15 | 1
111 | 15 | 5
112 | 13 | 5
113 | 14 | 2
114 | 14 | 3
115 | 14 | 4
116 | 14 | 5
117 | 12 | 1

Result shoud be:
--------------------------------------------------------------------------
EventId | EventName | RoomId_1 | RoomId_2 | RoomId_3 | RoomId_4 | RoomId_5
--------------------------------------------------------------------------
1 | Event1 | 1 | 0 | 0 | 0 | 0
2 | Event2 | 0 | 1 | 1 | 1 | 1
3 | Event3 | 0 | 0 | 0 | 0 | 1

Best regards!

Answer 1

Try this:

 /* outer query formats results */
 select EventID, EventName,
 case when RoomID = 1 then 1 else 0 end as Room1,
 case when RoomID = 2 then 1 else 0 end as Room2,
 case when RoomID = 3 then 1 else 0 end as Room3,
 case when RoomID = 4 then 1 else 0 end as Room4,
 case when RoomID = 4 then 1 else 0 end as Room5
 from (
          /* inner query makes the joins */
          select  b.eventid as EventID, a.name as EventName, c.roomid as RoomID
          from _event a inner join _eventTimePeriod b
          on a.id = b.eventid 
          inner join _eventTimePeriodRoom c 
          on c.eventtimeperiod = b.id
) v
order by EventID

I hope this helps you ..

SQL Fiddle Example