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/

.

pivot t-sql adding a percentage column

in a cross table result is very common to add a percentage column in respect

of the sum of another column

for example

x z%

a 2 0,46 = 2/(2+5+6)

b 5 0,38 = 5/(2+5+6)

c 6 0,15 = 6/(2+5+6)

is there an easy way to do that in t-sql using the pivot command ?

This was going to be my first answer

"Not easily that I can think of because it requires knowledge of the other rows in the result set and that is where SQL is not very good. That is performing calculations at two levels one aggregate is across th

You need to find the total and then calculate the percentage. This is normally done using a derived table"

However in digging I looked into windowing. This is a new feature that can be used with the new ranking functions as well as the normal aggregates.

In your situation you can do

select x, 1.0*x / sum(x) over (parition by 1) percentage

from mytable

What this does is that it performs a sum of x over the partition of 1 (as this is the same for each row it does a sum across the whole resultset).

Pivot Query or Cross Tab queries

I can't seem to figure out how to create a cross tab or pivot query on data.
If I was writing the SQL in Access I would write it like
TRANSFORM SUM(DTL.Amount) AS Amount
SELECT
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
FROM (SELECT
STC.DW_SELLER_ID AS ClientID
, STC.SELLER_NAME AS Client
, STC.SS_ID
, STC.TEAM_ACQ_NAME AS Region
, STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
, STC.[Product Consultant]
, CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
FROM dbo.tSeller_Team_Current AS STC
LEFT JOIN OPENQUERY(SSOT_DWDB,
'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
WHERE STC.Channel='RF'
AND STC.SELLER_ACTIVATION_DATE Is Not Null
AND SELLER_CLOSED_DATE Is Null) AS STC
LEFT JOIN dbo.tCRC_Details AS DTL
ON STC.ClientID=DTL.ClientID
GROUP BY
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
PIVOT DTL.[Description]
How could I write the query so it works in a View?
Thanks in advance.
Mark
I don't believe you can do crosstab queries within SQL Server like you can
in Access
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>I can't seem to figure out how to create a cross tab or pivot query on
>data.
> If I was writing the SQL in Access I would write it like
> TRANSFORM SUM(DTL.Amount) AS Amount
> SELECT
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> FROM (SELECT
> STC.DW_SELLER_ID AS ClientID
> , STC.SELLER_NAME AS Client
> , STC.SS_ID
> , STC.TEAM_ACQ_NAME AS Region
> , STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
> , STC.[Product Consultant]
> , CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
> FROM dbo.tSeller_Team_Current AS STC
> LEFT JOIN OPENQUERY(SSOT_DWDB,
> 'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
> FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
> ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
> WHERE STC.Channel='RF'
> AND STC.SELLER_ACTIVATION_DATE Is Not Null
> AND SELLER_CLOSED_DATE Is Null) AS STC
> LEFT JOIN dbo.tCRC_Details AS DTL
> ON STC.ClientID=DTL.ClientID
> GROUP BY
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> PIVOT DTL.[Description]
> How could I write the query so it works in a View?
> Thanks in advance.
> Mark
>
|||Thanks for your response Al. After scoping books and the help for several
hours that's the conclusion I came to also. Strange how the little baby
brother can do something that big brother can't. But that's as consistent as
the use of functions and naming conventions across MS software.
Have a great weekend.
Mark
"Al Newbie" wrote:

> I don't believe you can do crosstab queries within SQL Server like you can
> in Access
> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
> news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>
>