Hi all,
I have the following SQL Server query which retrieves rows i'm interested in:
Select Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion, Rank, Guide_Price, Actual_Price, Price_Cuts, Special_Offers, Size_Difference , Product_No, Store_Name
from Product
INNER JOIN #TempTable on Temp_Product_De sc_ID = Product.Product _DESC_ID
INNER JOIN Commodity on Product.Commodi ty_ID = Commodity.Commo dity_ID
INNER JOIN Store on Store.Store_ID = Product.Store_I D
INNER JOIN Section on Commodity.Secti on_ID = Section.Section _ID
INNER JOIN Department on Department.Dept _ID = Section.Dept_ID
INNER JOIN Description on Description.Pro duct_Desc_ID = Product.Product _Desc_ID
INNER JOIN Detail on Detail.Detail_I D = Product.Detail_ ID
and prodcues something like the following:
Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion, Rank, Guide_Price, Actual_Price
49140 HARDWARE COMPUTERS COMPUTERS CANON PRINTER 20 39.99 59.95
49140 HARDWARE COMPUTERS COMPUTERS CANON PRINTER 20 40.00 49.95
49141 HARDWARE COMPUTERS COMPUTERS EPSON PRINTER 20 59.99 73.00
49141 HARDWARE COMPUTERS COMPUTERS EPSON PRINTER 20 100.00 69.99
I need to pivot the above so only two lines are output. The Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion and Rank fields are all static and the remaining fields Guide_Price, Actual Price etc..are dynamic.
The output should look like:
Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion, Rank, Guide_Price, Actual_Price, Guide_Price, Actual_Price
49140 HARDWARE COMPUTERS COMPUTERS CANON PRINTER 20 39.99 59.95 40.00 49.95
49141 HARDWARE COMPUTERS COMPUTERS EPSON PRINTER 20 59.99 73.00 100.00 69.99
Can someone please provide an example of how i pivot this output using the above field names. Any help would be appreciated as i've been going around the houses on this!
Thanks in advance.
I have the following SQL Server query which retrieves rows i'm interested in:
Select Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion, Rank, Guide_Price, Actual_Price, Price_Cuts, Special_Offers, Size_Difference , Product_No, Store_Name
from Product
INNER JOIN #TempTable on Temp_Product_De sc_ID = Product.Product _DESC_ID
INNER JOIN Commodity on Product.Commodi ty_ID = Commodity.Commo dity_ID
INNER JOIN Store on Store.Store_ID = Product.Store_I D
INNER JOIN Section on Commodity.Secti on_ID = Section.Section _ID
INNER JOIN Department on Department.Dept _ID = Section.Dept_ID
INNER JOIN Description on Description.Pro duct_Desc_ID = Product.Product _Desc_ID
INNER JOIN Detail on Detail.Detail_I D = Product.Detail_ ID
and prodcues something like the following:
Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion, Rank, Guide_Price, Actual_Price
49140 HARDWARE COMPUTERS COMPUTERS CANON PRINTER 20 39.99 59.95
49140 HARDWARE COMPUTERS COMPUTERS CANON PRINTER 20 40.00 49.95
49141 HARDWARE COMPUTERS COMPUTERS EPSON PRINTER 20 59.99 73.00
49141 HARDWARE COMPUTERS COMPUTERS EPSON PRINTER 20 100.00 69.99
I need to pivot the above so only two lines are output. The Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion and Rank fields are all static and the remaining fields Guide_Price, Actual Price etc..are dynamic.
The output should look like:
Temp_Product_ID , Dept_Name, Section_Name, Commodity_Name, Product_Descrip tion, Rank, Guide_Price, Actual_Price, Guide_Price, Actual_Price
49140 HARDWARE COMPUTERS COMPUTERS CANON PRINTER 20 39.99 59.95 40.00 49.95
49141 HARDWARE COMPUTERS COMPUTERS EPSON PRINTER 20 59.99 73.00 100.00 69.99
Can someone please provide an example of how i pivot this output using the above field names. Any help would be appreciated as i've been going around the houses on this!
Thanks in advance.