Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Unanswered: rollup data rather than numbers?

    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!
    Last edited by bilderbach; 10-13-04 at 11:40. Reason: unclear

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21
    see if that makes more sense.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Does SQL2000 support a case statement?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Thanks!

    Thats juts what I was looking for!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •