title: Getting the top 6 items in a column to pivot to a row in SQL
body: <p>I'm having trouble getting a column to pivot in SQL. I'd like to pivot the top 6 results from one column into a row. The column I'm pivoting can have less than or more than 6 results to start with but I want to ignore anything beyond the top 6.</p>
  
  <p>My Table1 looks like this:</p>
  
  <pre><code>ID | GroupID | CodeNum
  ----------------------
  1  | 1       | 111
  2  | 1       | 222
  3  | 1       | 333
  4  | 1       | 444
  5  | 1       | 555
  6  | 1       | 666
  7  | 1       | 777
  8  | 2       | 111
  9  | 2       | 888
  10 | 3       | 999
  </code></pre>
  
  <p>And I want my output to look like this:</p>
  
  <pre><code>GroupID | Code1 | Code2 | Code3 | Code4 | Code5 | Code6
  -------------------------------------------------------
  1       | 111   | 222   | 333   | 444   | 555   | 666
  2       | 111   | 888   |       |       |       |
  3       | 999   |       |       |       |       |
  </code></pre>
  
  <p>I've tried this code:</p>
  
  <pre><code>SELECT  GroupID
          , [Code1] = CASE WHEN rn = 1 THEN CodeNum END
          , [Code2] = CASE WHEN rn = 2 THEN CodeNum END
          , [Code3] = CASE WHEN rn = 3 THEN CodeNum END
          , [Code4] = CASE WHEN rn = 4 THEN CodeNum END
          , [Code5] = CASE WHEN rn = 5 THEN CodeNum END
          , [Code6] = CASE WHEN rn = 6 THEN CodeNum END
  FROM    (
              SELECT  TOP 6 GroupID
                      , rn = ROW_NUMBER() OVER (ORDER BY ID)
                      , CodeNum
              FROM    Table1      
          ) q
  GROUP BY
          GroupID
  </code></pre>
  
  <p>But I'm getting an error that says <code>Column 'q.rn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</code></p>
  
  <p>Is there a way to do this when CodeNum could have between 1 and 12 values per GroupID?</p>