trying to flatten rows into columns

Question

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.

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.

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 

--trying to accomplish this with cte/partition:

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 

Answer 1

This data transformation is known as a PIVOT. In SQL Server 2005+ there is a function that will perform this process. :

select * from (   SELECT *    FROM mytable ) src pivot (   max(value)   for field in (first, last) ) piv 

See SQL Fiddle with Demo.

Or you can use an aggregate function with a CASE expression:

select rowid,   max(case when field = 'first' then value end) first,   max(case when field = 'last' then value end) last from MyTable group by rowid 

See SQL Fiddle with Demo.

You can also use multiple joins on your table:

select t1.rowid,   t1.value first,   t2.value last from mytable t1 left join mytable t2   on t1.rowid = t2.rowid   and t2.field = 'last' where t1.field = 'first' 

See SQL Fiddle with Demo

The result for all versions is the same:

| ROWID | FIRST |  LAST | ------------------------- |     1 |  neil | young | |     2 |   bob | dylan | |     3 |   tom | petty |