By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,534 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,534 IT Pros & Developers. It's quick & easy.

Dynamically Pivoting Rows to Columns in SQL Server 2008

P: 7
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_Description, Rank, Guide_Price, Actual_Price, Price_Cuts, Special_Offers, Size_Difference, Product_No, Store_Name
from Product
INNER JOIN #TempTable on Temp_Product_Desc_ID = Product.Product_DESC_ID
INNER JOIN Commodity on Product.Commodity_ID = Commodity.Commodity_ID
INNER JOIN Store on Store.Store_ID = Product.Store_ID
INNER JOIN Section on Commodity.Section_ID = Section.Section_ID
INNER JOIN Department on Department.Dept_ID = Section.Dept_ID
INNER JOIN Description on Description.Product_Desc_ID = Product.Product_Desc_ID
INNER JOIN Detail on Detail.Detail_ID = Product.Detail_ID


and prodcues something like the following:


Temp_Product_ID, Dept_Name, Section_Name, Commodity_Name, Product_Description, 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_Description 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_Description, 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.
Dec 17 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.