CODEWITHSUNDEEP
sqlsql-server
Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65481

Transform SQL Table

We have a table that has the following format

RecordID FieldName 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?

Upvotes: 2

Views: 174

Answers (4)

Manfred Sorg
Manfred Sorg

Reputation: 1890

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

Upvotes: 1

james-geldart
james-geldart

Reputation: 749

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

Upvotes: 0

Marek Grzenkowicz
Marek Grzenkowicz

Reputation: 17383

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'

Upvotes: 3

Ralf de Kleine
Ralf de Kleine

Reputation: 11745

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'

Upvotes: 0

Related Questions