Multiple records in one row

Hello,
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

I have already written the proc so that if prices aren't found for a
product at a particular location it will return null records. The part
I can't figure out is how to retrieve the records in the format that I
have shown above. I've got about 6+ hours in on this and haven't found
anything helpful enough on google or Books online yet so if someone
can help I will be very thankful.

Re: Multiple records in one row

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.

Re: Multiple records in one row

The number of products will be determined by the "items" table. Here
is the query as I have it before any rotating and such. The select *
is for ease of use while in development.

select permutations.location_key, permutations.location_name,
permutations.item, permutations.Currency_Code, pp.price --
permutations.*, pp.*
from
(select * from item
cross join location) permutations
left join productPrice pp
on permutations.item_key = pp.item_key and
permutations.location_key = pp.location_key
order by permutations.location_key, permutations.Item_key

Re: Multiple records in one row

Ray (csdeveloper06@gmail.com) writes:
> The number of products will be determined by the "items" table. Here
> is the query as I have it before any rotating and such. The select *
> is for ease of use while in development.
>
> select permutations.location_key, permutations.location_name,
> permutations.item, permutations.Currency_Code, pp.price --
> permutations.*, pp.*
> from
> (select * from item
> cross join location) permutations
> left join productPrice pp
> on permutations.item_key = pp.item_key and
> permutations.location_key = pp.location_key
> order by permutations.location_key, permutations.Item_key

And the contents of "items" may change depending on the mood of the
day?

Looks like you are in for a dynamic pivot, which you can't do in a single
SQL statement; you need to use dynamic SQL. Or a third-party product like
RAC, www.rac4sql.net.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx