Pivot Table Woes

I am very confused here. I really hope someone can help.

I have a table that contains "virtual fields" (ie. a column for field name and a column for field value). What I'd like is a pivot table that has the field names across the top and the field values as the row. I found the PivotTable service, but that seems like quite a bit of work for something that you can do in Access in a few clicks. I also know the SUM/CASE method, but unfortunately the virtual fields are dynamic, and I don't know what they could be named, nor how many of them exist. Does anybody have any ideas of what I can do? I'll include an example below to clear up any confusion.

Thanks!

What I have:

field_name | field_value

----------------

car_manufacturer | Jaguar

car_model | XJR

car_horsepower | 390

car_manufactuer | Ford

car_model | Mustang GT

car_horsepower | 400

What I want:

car_manufacturer | car_model | car_horsepower

------------------------------

Jaguar | XJR | 390

Ford | Mustang GT | 400Complete a primary key

car_manufacturer | Jaguar
car_manufactuer | Ford
car_model | XJR
car_model | Mustang GT
car_horsepower | 400
car_horsepower | 390

1 | car_manufacturer | Jaguar
1 | car_model | XJR
1 | car_horsepower | 390
2 | car_manufactuer | Ford
2 | car_model | Mustang GT
2 | car_horsepower | 400

and add an ordering table of value names

1 | car_manufacturer
2 | car_model
3 | car_horsepower|||create table dbo.ValuesTable(GroupId int,ValueName varchar(50),Value varchar(100) not null,primary key (GroupID,ValueName))
GO
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_manufacturer','Jaguar')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_model','XJR')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_horsepower','390')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_manufacturer','Ford')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_model','Mustang GT')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_horsepower','400')
GO

--without ordering
create function dbo.ufn_ConcatOrderingDistinct()
returns varchar(8000)
with schemabinding
as
begin
declare @.Res varchar(8000)
select @.Res = isnull(@.Res+',','')+'"'+ValueName+'"=max(case when d.ValueName = '''+ValueName+''''+' then d.Value end)'
from
(
select distinct ValueName
from dbo.ValuesTable
) x
return @.Res
end
GO
declare @.Exec varchar(8000)
set @.Exec='select '+dbo.ufn_ConcatOrderingDistinct()+'from dbo.ValuesTable d group by d.GroupId'
exec(@.Exec)
GO

--with ordering
create table dbo.OrderingTable(OrderId int primary key,ValueName varchar(50) not null)
GO
insert dbo.OrderingTable(OrderId,ValueName) values (1,'car_manufacturer')
insert dbo.OrderingTable(OrderId,ValueName) values (2,'car_model')
insert dbo.OrderingTable(OrderId,ValueName) values (3,'car_horsepower' )
GO
create function dbo.ufn_ConcatOrderingTable()
returns varchar(8000)
with schemabinding
as
begin
declare @.Res varchar(8000)
select @.Res = isnull(@.Res+',','')+'"'+ValueName+'"=max(case when d.ValueName = '''+ValueName+''''+' then d.Value end)'
from dbo.OrderingTable
order by OrderId
return @.Res
end
GO
declare @.Exec varchar(8000)
set @.Exec='select '+dbo.ufn_ConcatOrderingTable()+'from dbo.ValuesTable d group by d.GroupId'
exec(@.Exec)
GO|||create table #a (id int, fld varchar(40), val varchar(20))
insert #a select 1, 'car_manufacturer', 'Jaguar'
insert #a select 1, 'car_model', 'XJR'
insert #a select 1, 'car_horsepower', '390'
insert #a select 2, 'car_manufacturer', 'Ford'
insert #a select 2, 'car_model', 'Mustang GT'
insert #a select 2, 'car_horsepower', '400'
insert #a select 2, 'Doors', '4'

declare @.sql varchar(8000)

select @.sql = coalesce(@.sql+',','') + fld + ' = (select val from #a a1 where a1.fld = ''' + fld + ''' and a1.id = #a.id)'
from (select distinct fld from #a) as a
exec ('select id,' + @.sql+ ' from #a group by id')

drop table #a

gives
id car_horsepower car_manufacturer car_model Doors
---- ------- ------- ------- -------
1 390 Jaguar XJR NULL
2 400 Ford Mustang GT 4

You will have to split up the string if you have too many fields to fit in 8000 chars.