Transform SQL Table

We have a table that has the following format

RecordID Field开发者_运维技巧Name FieldValue

1         Name      John
1         Age       30
2         Name      Alice
2         Age       40

We would like to present this as:

John   30
Alice  40

Anyone have a good solution for this?

---

SELECT
  TN.FieldValue AS ValueName
, TV.FieldValue AS ValueAge
FROM dbo.Table1 TN
INNER JOIN dbo.Table1 TV ON TN.RecordID = TV.RecordID
  AND TN.FieldName = 'Name'
  AND TV.FieldName = 'Age'

---

Why didn't anybody use pivot?

declare @a TABLE(RecordId int, FieldName varchar(20), FieldValue varchar(20)) 

insert into @a select 1, 'Name', 'John' 
insert into @a select 1, 'Age', '30' 
insert into @a select 2, 'Name', 'Alice' 
insert into @a select 2, 'Age', '40' 

select *
from @a a pivot (max(FieldValue) for FieldName in (Name,Age)) p

---

declare @a TABLE(RecordId int, FieldName varchar(20), FieldValue varchar(20))

insert into @a select 1, 'Name', 'John'
insert into @a select 1, 'Age', '30'
insert into @a select 2, 'Name', 'Alice'
insert into @a select 2, 'Age', '40'

select 
 records.RecordId 
 , name.fieldvalue [Name]
 , age.fieldvalue [Age]
from
 (select distinct recordid from @a) records
 inner join @a name on records.recordid = name.recordid and name.fieldname = 'Name'
 inner join @a age on records.recordid = age.recordid and age.fieldname = 'Age'

---

This ain't pretty (but nor's the data), but this will work if your column names are known when you write the query:

SELECT 
RecordId, 
group_concat( if( FieldName = 'name', FieldValue, '' ) SEPARATOR '' ) AS person_name, 
group_concat( if( FieldName = 'age', FieldValue, '' ) SEPARATOR '' ) AS person_age
FROM test_table
GROUP BY RecordId

(works in MySQL 5.0)

You should also use CAST to get the fields to the correct type

If you don't know the fields, you'll need to (programatically) do a select distinct FieldName beforehand in order to build your query

I've a feeling the the Drupal Content Creation Kit (CCK) does something rather similar to this