Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    3

    Question Unanswered: 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:

    Code:
    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:

    Code:
    car_manufacturer	|	car_model		|	car_horsepower
    
    -------------------------------------------------------------------------------------------------
    
    Jaguar		|	XJR		|	390
    
    Ford		|	Mustang GT	|	400

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Complete a primary key

    Code:
    car_manufacturer	|	Jaguar
    car_manufactuer	|	Ford
    car_model		|	XJR
    car_model		|	Mustang GT
    car_horsepower	|	400
    car_horsepower	|	390
    Code:
    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

    Code:
    1 | car_manufacturer
    2 | car_model	
    3 | car_horsepower

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •