I need to rollup a bunch of detail records to a single line report like a groupby sum() rollup using data rather than numbers and I would like to do it with sql rather than in my vb code.
Its basically a bunch of detail attributes of a product that I would like to generate a query returning one line per product based on a predefined set of attributes.
The data only has 3 columns:
codename = the product code
recordtype = the type of attribute
(for example price,weight,height,width,lastshipment)
data = the data value of the attribute
Sample data would be like this:
Output columns should look like this:
Code, Price, Weight, Height, Width
The output data from the sample above should look like this:CODE1,d1,d2,d3,d5
note in CODE1 I have a LASTSHIP type and I dont care about it, I just want the 4 types for this query.
I am using MSSQL 2000.
I am wide open to a better way to do this, I just need the ability to assign an unlimited number of attributes to a specific product and this was how I best figured out how to accomplish it giving the flexibility of any type of record.
Hope thats clearer...?
Thanks for your help!
Last edited by bilderbach; 10-13-04 at 11:40.
I'm not sure that I follow the rules for the rollup, or the exact format you want the results (I can't figure it out from your examples). It would also help a lot to know which database engine you are using, since this kind of rollup is much easier to do with engine specific tricks than doing it using pure SQL.
, Min(CASE WHEN 'PRICE' = a.recordtype THEN a.data END) AS Price
, Min(CASE WHEN 'WEIGHT' = a.recordtype THEN a.data END) AS Weight
, Min(CASE WHEN 'HEIGHT' = a.recordtype THEN a.data END) AS Height
, Min(CASE WHEN 'WIDTH' = a.recordtype THEN a.data END) AS Width
FROM myTable AS a
GROUP BY a.CODENAME