title: Multiple Column Pivot in T-SQL
body: <p>I am working with a table where there are multiple rows that I need pivoted into columns.  So the pivot is the perfect solution for this, and works well when all I need is one field.  I am needing to return several fields based upon the pivot.  Here is the pseudo code with specifics stripped out:</p>
  
  <pre><code>SELECT 
    field1,
    [1], [2], [3], [4]
  FROM
    (
    SELECT 
      field1, 
      field2, 
      (ROW_NUMBER() OVER(PARTITION BY field1 ORDER BY field2)) RowID
    FROM tblname
    ) AS SourceTable
  PIVOT
    (
    MAX(field2)
    FOR RowID IN ([1], [2], [3], [4])
    ) AS PivotTable;
  </code></pre>
  
  <p>The above syntax works brilliantly, but what do I do when I need to get additional information found in field3, field4....?</p>