rollup data rather than numbers?

bilderbach · #1 (**permalink**) 10-12-04, 19:38

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:
CODE1,PRICE,d1
CODE1,WEIGHT,d2
CODE1,HEIGHT,d3
CODE1,WIDTH,d4
CODE1,LASTSHIP,1/1/2004
CODE2,HEIGHT,d6
CODE3,PRICE,d7
CODE3,HEIGHT,d8
CODE3,WIDTH,d9

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
CODE2,null,null,d6,null
CODE3,d7,null,d8,d9

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!

Pat Phelan · #2 (**permalink**) 10-12-04, 23:03

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.

-PatP

bilderbach · #3 (**permalink**) 10-13-04, 10:43

see if that makes more sense.

urquel · #4 (**permalink**) 10-13-04, 16:41

Does SQL2000 support a case statement?

Pat Phelan · #5 (**permalink**) 10-13-04, 17:15

I'd use:

Code:

SELECT a.CODENAME
,  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

-PatP

bilderbach · #6 (**permalink**) 10-22-04, 20:56

Thats juts what I was looking for!

Internet Services

Web Development

Digital Marketing

Consumer Tech