Variable number of columns

[gdtrfb]

Maybe this is an easy problem, maybe it isn't...but I'm sure it's been solved a million times before so I'm hoping the Collective Knowledge will come through here...

I have a table that looks like this:

widgetID (INT)
attributeID (INT)
attribute_value (varchar)

The table has a compound primary key consisting of the widgetID and the attributeID. These relate to other tables that give us attribute_name and widget_type, etc. but that's not pertinent to this discussion.

There can be a variable number of records in the table for a given widgetID. All widgetIDs do not have the same number of attributes. Some widgetIDs share common attributes, some attributes are unique to specific widgetIDs.

I need to generate a report that looks like this

widgetID attr1 attr2 attr3 ... attrN

for all the records in the widget_attribute table. Remember that the number of attributes per widget is variable and can be as high as 20 or more. And count(distinct(attributeID)) is currently over 100.

I know, it's a big messy table but that's what they want.

I'm currently doing this with a couple temporary tables and loops but that's taking forever. I'm hoping there's an elegant solution to this type of problem.

Thanks!