November 9, 2009 at 1:38 pm
I have a Product table that desscibes all the attributes of a product. The attributes comes from the Attributes table not shown here. When ever a new product is added we list all the new attributes belonging to that new product in the Attributes table - which means only the new attributes which alrerady don't exist in the Product attribute table go in it.
Here is my Product table:
PROD_IDProd_AttributeProd_Desc
------------------------------
123ColorBlue
123MemberId3245
123Number74232566
123TypeTerminal
456ColorWhite
456Number98766332
456Pin99999999
456TypeCallCard
Desired Output: I would like to transpose the above table to look like below. My Prod_Attribute data from Products table becomes columns.
PROD_IDCOLORMemberIdTypeNumberPin
---------------------------------
123Blue3245Terminal74232566NULL
456White98766332CallCardNULL99999999
Note1: The attributes are not fixed...if today we have 10 attributes, tomorrow when a new product is introduced we might add 2 new attrubutes that don't already exist.
Note2: I tried using PIVOT but the problem with PIVOT is I have to use aggregate, where as I don't have any column to aggregate on. Please let me know if this can still be accomplished through PIVOT? OR any other solution is fine as well. Please help!!
Also, is there a better way to model a Product Catalog where new products are added frequently where inturn attributes get added frequiently. We could have modeled the Product table as the desired output above, but then we would have to add extra column every time a new attribute for the new product needs to be added. We didn't want to change the table structure by adding columns every time a new product is introduced, but rather keep the attributes at the data level.
All help is very greatly appreciated. Thanks in advance for all the help provided 🙂
November 9, 2009 at 2:11 pm
What you're looking for is "dynamic cross-tab". Do a search for that. Phil Factor has an article on it on simple-talk.com, and Jeff Moden has one on this site.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2009 at 6:10 pm
Even a cross-tab is going to do an aggregation. I use MAX() as the aggregation function.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply