Turn multiple records into 1 record?

[bzzyplayin]

My dilemma, in which I need urgent help with, is with a Lawson table. We are just starting to build attributes. They allow 9,999 user-defined attributes. So, the data ends up in the following format (with multiple records per ObjectID):

ObectID AttributeName Value
12 SQFT 1200
12 StoreType Mall
12 State MI
15 SQFT 3000
15 StoreType Mall
15 State IN

We are unable to use this format to join to any other tables, since the above example would inflate the other records (by 2) if I join by ObjectID. I need one record per ObjectID.

What we hope to do, is create a table in SQL that puts the information by ObjectID into 1 record, with each AttributeName as a field heading with the corresponding value for that field. Is this possible?

ObjectID SQFT StoreType State
12 1200 Mall MI
15 3000 Mall IN

The fields needs to be dynamic based upon additional attributes that are created.

Note: This new table would be used for reporting using Crystal Reports 8.5.

Any help or advice will be more than appreciated.

 

[cmgaviao]

I'm not sure if this is the best way, but you could accomplish this like this: Mind you that for each new attribute you'll have to add new criteria an fields:

Select tbl1.ObjectId, tbl1.AttributeName, tbl1.Value, tbl2.attributename, tbl2.value, tbl3.attributename, tbl3.value
from datatable as tbl1, datatable as tbl2, datatable as tbl3

where tbl1.attributename = 'sqft'
and tbl2.attributename = 'storetype'
and tbl1.objectid *= tbl2.objectID
and tbl3.attributename = 'state'
and tbl1.objectid *= tbl3.objectID

You could also accomplish the same with unions I believe...Perhaps one of the real gurus knows of a way to make it truly dynamic...

Pardon the Non-Ansi SQL, but as its the first thing I learned, I'll probably stick too it until MS stops supporting it...

 

[codestorm]

Here's something really ugly I just tinkered with which does much of what you want.

I've got to get back to work now, but I'm sure you'll be able to get the rest.

Disclaimer: It uses dynamic SQL, so messes up nice, clean security measures

declare @test1 table (NewColumn varchar(100), colid int identity(1,1))
insert @test1 (newcolumn)
select SD_Key
from A_SessionDetail
group by SD_Key
declare @a int, @b int, @c varchar(100), @cmd varchar(1000)
select @a = min(colid), @b = max(colid) from @test1
create table #test2 (objid varchar(100) primary key)
while @a < @b begin
select @c = newcolumn from @test1 where colid = @a
set @cmd = 'alter table #test2 add ' + @c + ' varchar(100)'
exec (@cmd)
select @a = min(colid) from @test1 where colid > @a
end
select * from #test2
declare @d varchar(100), @e varchar(100)
select @d = min(sD_key), @e = max(sd_key) from a_sessiondetail
while @d < @e begin
print @d
--loop thru items table; if exists, update else insert. needs to be dynamic sql as above @cmd (to pick column for ins/upd)
select @d = min(sd_key) from a_sessiondetail where sd_key > @d
end
drop table #test2


codestorm
Fire bad. Tree pretty. - Buffy
Kludges are like lies.
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[IT2003]

If you know all the AttributeNames in advance (even if you have not used that attribute for a particular object) this may work.


select dfl.objectid, max(dfl.sqft) sqft , max(storetype) storetype, max(state) state from
(
select objectid, avalue as sqft, '' as storetype, '' as state from oa where aname = 'sqft'
union all
select objectid, '' as sqft, avalue as storetype, '' as state from oa where aname = 'storetype'
union all
select objectid, '' as sqft, '' as storetype, avalue as state from oa where aname = 'state') dfl
group by dfl.objectid