title: Fast query for end user report
body: <p>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,</p>
  
  <pre><code>tblEvent (ID, Name) 
  tblEventTimePeriod (ID, EventId)
  tblEventTimePeriodRoom (ID, EventTimePeriodId, RoomId)
  </code></pre>
  
  <p>Relationships between tables:</p>
  
  <pre><code>tblEvent to tblEventTimePeriod -&gt; One to many
  tblEventTimePeriod to tblEventTimePeriodRoom -&gt; many to many
  </code></pre>
  
  <p>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.</p>
  
  <p>My problem is - how to build fast query to get result as below:</p>
  
  <pre><code>EventId | EventName | RoomId_1 | RoomId_2 | RoomId_3 | RoomId_4 | RoomId_5
  </code></pre>
  
  <p>RoomId_X - meens than Event has reserved RoomId = X. It doesn't matters which tblEventTimePeriod has this reservation.</p>
  
  <p>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 :)</p>
  
  <p>I will be grateful for any hints! I'm using SQL Server 2008 R2.</p>
  
  <p>Example data:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Best regards!</p>