title: trying to flatten rows into columns
body: <p>I have a group of rows in a table that have an id.  I am trying to flatten it out in rows with multiple column.  I am almost certain I have done this with a cte and maybe partition.</p>
  
  <p>I have used cte's to delete duplicate data and I thought I has done something similar to what I am trying to accomplish here.  I was able to come up with workable solution (listed below) but still feel like a more elegant solution should be available.</p>
  
  <pre><code>CREATE TABLE #MyTable ( RowID int , field VARCHAR(10), value  VARCHAR(10))  
  
  INSERT INTO #MyTable ( RowID, field, value ) VALUES  ( 1, 'first', 'neil' )
  
  INSERT INTO #MyTable ( RowID, field, value ) VALUES  ( 2, 'first', 'bob'  )
  
  INSERT INTO #MyTable ( RowID, field, value ) VALUES  ( 3, 'first', 'tom'  )
  
  INSERT INTO #MyTable ( RowID, field, value ) VALUES  ( 1, 'last', 'young' )
  
  INSERT INTO #MyTable ( RowID, field, value ) VALUES  ( 2, 'last', 'dylan' )
  
  INSERT INTO #MyTable ( RowID, field, value ) VALUES  ( 3, 'last', 'petty' )
  
  SELECT * FROM #mytable
  </code></pre>
  
  <p>--trying to accomplish this with cte/partition:</p>
  
  <pre><code>SELECT rowid, 
     [first] = (Select value FROM #mytable where field = 'first' and rowid = t.rowid), 
     [last] = (Select value FROM #mytable where field = 'last' and rowid = t.rowid)
  FROM #mytable t
  GROUP BY rowid
  </code></pre>