Help with dynamically pivoting rows in SQL Server!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with dynamically pivoting rows in SQL Server!

Author

Topic

Orbison
Starting Member

14 Posts

Posted - 2009-12-16 : 11:05:11

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.

Sachin.Nand

2937 Posts

Posted - 2009-12-17 : 04:45:27

If you are using SQL 2005 then have a look at this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136989

PBUH

Orbison
Starting Member

14 Posts

Posted - 2009-12-17 : 05:13:27

Hi Idera,
Many thanks for getting back to me, i'm using SQL 2008! I had a look at your post and just wondering is there an easy way to do this using the pivot Statement or will i need to loop through the records first as per your example?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources