|
|
Posted by Ed Murphy on 09/09/07 21:48
Ray wrote:
> I have a table that contains multiple prices for multiple
> location (yes I know is should have been done with an Xref table or
> something, but I didn't create it and it's too late to do right now).
> The records are similar to this...
> priceID, productID (non-unique), productName, locationID, price
> 1,100, prod1, 1, $3.00
> 2,101, prod2, 1, $4.00
> 3,102, prod3, 1, $2.00
> 4,101, prod1, 2, $9.00
> 5,102, prod2, 2, $5.00
> 6,103, prod3, 2, $8.00
>
> What I would like is for the output is
>
> location1Name, Product1Price, Product2Price, Product3Price
> location2Name, Product1Price, Product2Price, Product3Price
> location3Name, Product1Price, Product2Price, Product3Price
Will you ever care about more than these three products? If not, then:
select location.locationID, max(location.locationName),
max(case prices.productID when 101 then prices.price end) Prod1Price,
max(case prices.productID when 102 then prices.price end) Prod2Price,
max(case prices.productID when 103 then prices.price end) Prod3Price
from prices
join locations on prices.locationID = locations.locationID
group by location.locationID
order by location.locationID
If the set of products may change, but the set of locations will
rarely do so, then swap their roles throughout:
select products.productID, max(products.productName),
max(case prices.locationID when 1 then prices.price end) Loc1Price,
max(case prices.locationID when 2 then prices.price end) Loc2Price,
max(case prices.locationID when 3 then prices.price end) Loc3Price,
from prices
join products on prices.productID = products.productID
group by products.productID
order by products.productID
(Side note: The only location-independent product data in the prices
table should be the primary key i.e. productID; productName should be
removed, and retrieved from a products table instead. Provided that
the products table is indexed on productID, this should be efficient.)
If both the set of products and the set of locations may change, then I
recommend you just do a straight query of the prices table, and let your
reporting layer (e.g. Excel, Crystal Reports) do the cross-tab work.
[Back to original message]
|