title: Scalable alternative to dozens of joins to the same table?
body: <p>I have a base table with primary key <code>BaseTableID</code> and an events table that represents multiple events for a single record in the base table, i.e.</p>
  
  <pre class="lang-none prettyprint-override"><code>BaseTableID | EventCode | EventSequenceNumber
  ------------|-----------|--------------------
  1           | A123      | 1
  1           | A557      | 2
  1           | 45AB      | 3
  1           | 0987      | 4
  ...
  1           | SD12      | 70
  2           | Z902      | 1
  2           | D92C      | 2
  ... etc ...
  </code></pre>
  
  <p>I need to denormalize this in order to provide a flat file in the format:</p>
  
  <pre class="lang-none prettyprint-override"><code>BaseTableID | Event01 | Event02 | Event03 | ... | Event70
  ------------|---------|---------|---------|-----|---------
  1           | A123    | A557    | 45AB    | ... | SD12
  2           | Z902    | D92C    |
  ... etc ...
  </code></pre>
  
  <p>I <em>can</em> currently achieve this with the query</p>
  
  <pre class="lang-sql prettyprint-override"><code>select BaseTable.BaseTableID,
         Event01 = Event01.EventCode,
         Event02 = Event02.EventCode,
         Event03 = Event03.EventCode,
         Event04 = Event04.EventCode,
         ...
  from BaseTable
  
  left join Events Event01
  on BaseTable.BaseTableID = Event01.BaseTableID
  and Event01.EventSequenceNumber = 1
  
  left join Events Event02
  on BaseTable.BaseTableID = Event02.BaseTableID
  and Event02.EventSequenceNumber = 2
  
  left join Events Event03
  on BaseTable.BaseTableID = Event03.BaseTableID
  and Event03.EventSequenceNumber = 3
  
  left join Events Event04
  on BaseTable.BaseTableID = Event04.BaseTableID
  and Event04.EventSequenceNumber = 4
  
  ... etc...
  </code></pre>
  
  <p>Which <em>works</em>, but scales horribly and the number of joins / columns is entirely dependent on <code>select max(EventSequenceNumber) from Events</code>, which could (and is likely to) change over time. Imagine the nightmare situation when one base table record ends up with hundreds of events. I don't want to copy-paste hundreds of joins!</p>
  
  <p>(Worth noting - the format of required output is well outside my control. I can't change that to something more sensible)</p>
  
  <p><strong>There must be a better way</strong>. Is there?</p>