Denormalizing Data

[jdenio]

I have the following 3 Tables:



PRODUCTS: productcode, productcode, productname, minqty, vendor_price, productprice
DISCOUNTS: discountautoid, name, minqty, maxqty, discounttype, discountvalue
DISCOUNTS_APPLYTO: syncid, discountautoid, productcode

I have a query as follows:

SELECT
Products.productcode,
Products.productname,
Products.minqty AS multiples,
Products.vendor_price,
Products.productprice,
Discounts_ApplyTo.syncid,
Discounts.name,
Discounts.minqty,
Discounts.maxqty,
Discounts.discounttype,
Discounts.discountvalue
FROM
(Discounts INNER JOIN Discounts_ApplyTo ON Discounts.discountautoid = Discounts_ApplyTo.discountautoid)
INNER JOIN
Products ON Discounts_ApplyTo.productcode = Products.productcode;

There can be 1 to 5 Discount levels in this data. Below is an example of results of the above query:

productcode, productname, multiples, vendor_price, productprice, syncid, name, minqty, maxqty, discounttype, discountvalue
================================================== ===========================================
ABC ABC Product 15 100.00 150.00 1 DISC1 1 100 Per Unit 10.00
ABC ABC Product 15 100.00 150.00 2 DISC1 101 200 Per Unit 15.00
ABC ABC Product 15 100.00 150.00 3 DISC1 201 300 Per Unit 20.00
ABC ABC Product 15 100.00 150.00 4 DISC1 301 400 Per Unit 25.00
ABC ABC Product 15 100.00 150.00 5 DISC1 401 Per Unit 30.00
XYZ XYZ Product 25 200.00 275.00 6 DISC2 1 50 Per Unit 10.00
XYZ XYZ Product 25 200.00 275.00 7 DISC2 51 100 Per Unit 20.00
XYZ XYZ Product 25 200.00 275.00 8 DISC2 101 Per Unit 30.00

I need to denormalize the data so that I get a result with the following:
productcode, productname, multiples, vendor_price, productprice, minqty1, maxqty1, discval1, minqty2, maxqty2, discval2, minqty3, maxqty3, discval3, minqty4, maxqty4, discval4, minqty5, maxqty5, discval5

Notes:
There will never be more than 5 discount levels, but could be less.
Min and Max Qtys. set quantity discounts.
The primary key for the discounts is SYNCID, I've noticed that other denormalization posts use a SETTING1, SETTING2, etc... value in what would be the discount table in this case to match a value to the new column heading. There is no repeating data like DISCOUNT1, DISCOUNT2, DISCOUNT3 for each product that could be used to assign a discount record to the new denormalized column. However, the way the discounts are added to the individual columns must be sorted by the syncid. EXAMPLE:

If the discount data is:
syncid, minqty, maxqty, discountvalue
=====================================
45 1 100 10
46 101 200 20
47 201 30
48 1 249 10
49 250 499 15
50 500 749 20
51 750 999 25
52 1000 30

(1) HOW WOULD I CREATE SQL TO NUMBER THE DISCOUNTS TO GIVE SPECIFIC REPEATING DATA? RESULT AS BELOW (This question is based on solutions that require a repeating identifier):

syncid, seqid, minqty, maxqty, discountvalue
============================================
45 d1 1 100 10
46 d2 101 200 20
47 d3 201 30
48 d1 1 249 10
49 d2 250 499 15
50 d3 500 749 20
51 d4 750 999 25
52 d5 1000 30


(2) HOW WOULD I GET THE BELOW AS A DENORMALIZED RESULT?:
prodcode, prod name, minqty1, maxqty1, discval1, minqty2, maxqty2, discval2, minqty3, maxqty3, discval3, minqty4, maxqty4, discval4, minqty5, maxqty5, discval5
================================================== ================================================== ======================================================= =====
ABC ABC Product 1 100 10 101 200 20 201 30
XYZ XYZ Product 1 249 10 250 499 15 500 749 20 750 999 25 1000 30

Notice that first record did not have records for levels 4 and 5.

I tried very hard to include everything to answer this question any help would be AWESOME!!! thank you!

[June7]

To do this with a CROSSTAB would require a field that provides a 1-5 sequence number for each product group's records. The sequence field would be used to produce the column headers for the CROSSTAB.

Generating that sequence ID by calculation in query would be done with either a subquery or a DCount() expression. However, I am not certain that calculated field could be used in a CROSSTAB.

Making a single CROSSTAB for multiple sets of values as you want has its own issues http://allenbrowne.com/ser-67.html#MultipleValues

I envision a series of queries (maybe 5) to get the final output.

Alternatives involve VBA:

1. writing records to a temp table

2. http://allenbrowne.com/func-concat.html