February 10, 2003 at 12:11 am
I have tables
products
ProductID
ItemId
PriceCodeID
Prices
ItemId
Code1
Code2
Code3
Code4
Where the PriceCodeID relates to a column name.
I am thinking I need some type of pivot table, but have had no experience with these.
Ken
If you don't ask you never get an anwser.
If you don't ask you never get an anwser.
February 10, 2003 at 2:07 am
Do you have a fixed number of PriceCodeID's which you wish to pivot or can the number of values vary.
The manner in which a pivot is achieved will vary considerably depending upon whether the column names are fixed or varying.
February 10, 2003 at 2:17 am
Sorry I should have stated that it is fixed, 6 columns
Edit: Does the number of columns coming from the other table matter?
Ken
If you don't ask you never get an anwser.
Edited by - The Scarecrow on 02/10/2003 02:18:02 AM
If you don't ask you never get an anwser.
February 10, 2003 at 3:13 am
Having re-read your initial post I think that what you are trying to acheive is a join between the tables which subject to the value in the PriceCodeID field (which is the name of a column in the 2nd table) you would like to return the value held in the Prices table......and not perform a pivot.
If my interpretation is correct, try this.
selectProducts.ProductID,
Products.ItemID,
case Products.PriceCodeID
when 'Code1' then Prices.Code1
when 'Code2' then Prices.Code2
when 'Code3' then Prices.Code3
when 'Code4' then Prices.Code4
end as PriceCode
fromProducts inner join Prices on Products.ItemID = Prices.ItemID
February 10, 2003 at 3:19 am
Yes I think that is it!! can not believe I missed that. I think my mind was stuck on trying to join the tables by the value in the row with the column name.
Thanks
Ken
If you don't ask you never get an anwser.
If you don't ask you never get an anwser.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply