title: How can I query row data as columns?
body: <p>I'm sure I'm missing something here.</p>
  
  <p>I have a dataset like this:</p>
  
  <pre>
  FK    RowNumber    Value    Type    Status
  1     1            aaaaa    A       New
  1     2            bbbbb    B       Good
  1     3            ccccc    A       Bad
  1     4            ddddd    C       Good
  1     5            eeeee    B       Good
  2     1            fffff    C       Bad
  2     2            ggggg    A       New
  2     3            hhhhh    C       Bad
  3     1            iiiii    A       Good
  3     2            jjjjj    A       Good
  </pre> 
  
  <p>I'd like to query the top 3 results and Pivot them as columns, so the end result set looks like this:</p>
  
  <pre>
  FK    Value1    Type1    Status1    Value2    Type2    Status2    Value3    Type3    Status3
  1     aaaaa     A        New        bbbbb     B        Good       ccccc     A        Bad
  2     fffff     C        Bad        ggggg     A        New        hhhhh     C        Bad
  3     iiiii     A        Good       jjjjj     A        Good
  
  </pre>
  
  <p>How can I accomplish this in SQL Server 2005? </p>
  
  <p>I have been attempting this using <a href="http://msdn.microsoft.com/en-us/library/ms177410%28SQL.105%29.aspx" rel="nofollow noreferrer">PIVOT</a>, but I am still very unfamiliar with that keyword and cannot get it to work the way I want.</p>
  
  <pre><code>SELECT * --Id, [1], [2], [3]
  FROM
  (
      SELECT Id, Value, Type, Status
      , ROW_NUMBER() OVER (PARTITION BY Id ORDER Status, Type) as [RowNumber]
      FROM MyTable
  ) as T
  PIVOT
  (
      -- I know this section doesn't work. I'm still trying to figure out PIVOT
      MAX(T.Value) FOR RowNumber IN ([1], [2], [3]),
      MAX(T.Type) FOR RowNumber IN ([1], [2], [3]),
      MAX(T.Status) FOR RowNumber IN ([1], [2], [3])
  ) AS PivotTable;
  </code></pre>
  
  <p>My actual data set is a bit more complex than this, and I need the top 10 records, not the top 3, so I don't want to simply do <code>CASE WHEN RowNumber = X THEN...</code> for each one.</p>
  
  <p><strong>Update</strong></p>
  
  <p>I tested all the answers below, and found most of them seem about the same with no apparent performance difference in smaller data sets (around 3k records), however there was a slight difference when running the queries against larger data sets. </p>
  
  <p>Here are the results of my tests using 80,000 records and querying for 5 columns in the top 10 rows, so my end result set was 50 columns + the <code>Id</code> column. I'd suggest you test them on your own to decide which one works best for you and your environment.</p>
  
  <ul>
  <li><p><a href="https://stackoverflow.com/a/12221580/302677">bluefoot's answer</a> of unpivoting and re-pivoting the data averaged the fastest at about 12 seconds. I also liked this answer because I found it easiest to read and maintain.</p></li>
  <li><p><a href="https://stackoverflow.com/a/12221510/302677">Aaron's answer</a> and <a href="https://stackoverflow.com/a/12221623/302677">koderoid's answer</a> both suggest using a <code>MAX(CASE WHEN RowNumber = X THEN ...)</code>, and was close behind averaging at around 13 seconds.</p></li>
  <li><p><a href="https://stackoverflow.com/a/12221475/302677">Rodney's answer</a> of using multiple <code>PIVOT</code> statements averaged around 16 seconds, although it might be faster with fewer PIVOT statements (my tests had 5).</p></li>
  <li><p>And the first half of <a href="https://stackoverflow.com/a/12221510/302677">Aaron's answer</a> that suggested using a CTE and <code>OUTER APPLY</code> was the slowest. I don't know how long it would take to run because I cancelled it after 2 minutes, and that was with around 3k records, 3 rows, and 3 columns instead of 80k records, 10 rows, and 5 columns.</p></li>
  </ul>