title: SQL: Pivot with placeholder rows
body: <p>I have a table that stores one row for episodes of a show. Note that not every episode is stored. </p>
  
  <pre><code>showID    |    viewerID   |   episodeNumber    |    episodeDescription
  ____________________________________________________________________
  15        |    ID-123     |      1             |    Scary
  ____________________________________________________________________
  15        |    ID-123     |      3             |    Funny
  </code></pre>
  
  <p>I've built a pivot query to create columns for episode numbers and descriptions. Number of episodes is a known quantity. </p>
  
  <pre><code>showID    |    viewerID    |    episode1    |    episode2    |    episode3 
  __________________________________________________________________________
  15         |   ID-123      |    Scary       |     funny       |     
  </code></pre>
  
  <p>My problem is that where there is no record for an episode, as shown in this example by the lack of row for episode number 2 in the first table, the query does not create a "place holder" and instead puts the data for episode3 into the episode2 column. I don't even know if what I need is possible -- but I do need to somehow make sure the episode number is associated with the correct description when there is no data (no row) for an episode number, and I do need to show a column for every episode in table 2 (so in table 2 above, the field for episode2 should be blank).</p>
  
  <p>My query follows: </p>
  
  <pre><code>SELECT viewerID, showID, 
  episodeDescription1, episodeDescription2, episodeDescription3
  FROM
  (
    SELECT viewerID, showID, col+cast(seq as varchar(10)) as col, value
    from 
    (
      SELECT viewerID, showID, episodeNumber, episodeDescription,
             row_number() over(partition by showID
                          order by episodeNumber) seq
      FROM tbl_showEpisodes
    ) d
    CROSS APPLY
    (
     SELECT 'episodeNumber', cast(episodeNumber as varchar(20)) 
     UNION ALL
     SELECT 'episodeDescription', episodeDescription
    ) c (col, value)
  ) src
  pivot
  (
    MAX(value)
    for col in (
      episodeNumber1, episodeDescription1, episodeNumber2, 
      episodeDescription2, episodeNumber3, episodeDescription3)
    ) piv;
  </code></pre>
  
  <p>I hope this is clear, and thanks in advance for any help! Using SQL Server 2012.  </p>