PIVOT/CROSS TAB/Converting Rows to (multiple group) Columns

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:

CategoryID ProductID ProductName Month UnitPrice QtySold SalesAmount 1 1 Panel Jan 5 10 50 1 1 Panel Feb 5 15 75 1 1 Panel Mar 5 20 100 1 2 Frame Jan 10 30 300 1 2 Frame Feb 10 25 250 1 2 Frame Mar 10 20 200 1 3 Glass Jan 20 10 200 1 3 Glass Feb 20 20 400 1 3 Glass Mar 20 30 600

I would like it to be converted into following result set:

CategoryID ProductID ProductName UnitPrice QtySold_Jan SalesAmt_Jan QtySold_Feb SalesAmt_Feb QtySold_Mar SalesAmt_Mar 1 1 Panel 5 10 50 15 75 20 100 1 2 Frame 10 30 300 25 250 20 200 1 3 Glass 20 10 200 20 400 30 600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

What you are attempting to do is BEST done with the client application. SQL Server excels at storing and retreiving data. These kinds of 'transformations', while possible, are not the best use of a very expensive resource.

However, if you must, these articles demonstrate several variations of how to accomplish your goal -and they offer 'concrete' examples

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

.