Cross Tab Report

Hi,

I need some help on crosstab query. I have tried the examles, but somehow, not able to find a right solution.

Any help will be appreciated. The report I would like to have is the one at the bottom.

thx.

Josh

Table Structure>

Brand Product Line Jan Feb Mar Total A Chocolate Volume 500 200 100 800 B FizzDrink Volume 250 300 200 750 A Chocolate Volume 230 100 120 450 A Chocolate Revenue 2,000 600 350 2,950 B FizzDrink Revenue 1,000 700 250 1,950 A Chocolate Revenue 800 400 550 1,750 A Chocolate AvgEarningPerProduct 4.00 3.00 3.50 3.69 B FizzDrink AvgEarningPerProduct 4.00 2.33 1.25 2.60 A Chocolate AvgEarningPerProduct 3.48 4.00 4.58 3.89

How the resultset should look like>

Brand Product Volume Revenue AvgEarningPerProduct Month A Chocolate 500 2,000 4.00 Jan A Chocolate 200 600 3.00 Feb A Chocolate 100 350 3.50 Mar A Chocolate 800 2,950 3.69 Total A Chocolate 230 800 3.48 Jan A Chocolate 100 400 4.00 Feb A Chocolate 120 550 4.58 Mar A Chocolate 450 1,750 3.89 Total B FizzDrink 250 1,000 4.00 Jan B FizzDrink 300 700 2.33 Feb B FizzDrink 200 250 1.25 Mar B FizzDrink 750 1,950 2.60 Total

This is not a crosstab report but an anti-crosstab report. Assuming SQL Server 2005, have you tried the new UNPIVOT function to unpivot the dataset?

Cross Tab Query

Hi,

I need some help on crosstab query. I have tried the examles, but somehow, not able to find a right solution.

Any help will be appreciated.

thx.

Josh

Table Structure>

Brand Product Line Jan Feb Mar Total A Chocolate Volume 500 200 100 800 B FizzDrink Volume 250 300 200 750 A Chocolate Volume 230 100 120 450 A Chocolate Revenue 2,000 600 350 2,950 B FizzDrink Revenue 1,000 700 250 1,950 A Chocolate Revenue 800 400 550 1,750 A Chocolate AvgEarningPerProduct 4.00 3.00 3.50 3.69 B FizzDrink AvgEarningPerProduct 4.00 2.33 1.25 2.60 A Chocolate AvgEarningPerProduct 3.48 4.00 4.58 3.89

How the resultset should look like>

Brand Product Volume Revenue AvgEarningPerProduct Month A Chocolate 500 2,000 4.00 Jan A Chocolate 200 600 3.00 Feb A Chocolate 100 350 3.50 Mar A Chocolate 800 2,950 3.69 Total A Chocolate 230 800 3.48 Jan A Chocolate 100 400 4.00 Feb A Chocolate 120 550 4.58 Mar A Chocolate 450 1,750 3.89 Total B FizzDrink 250 1,000 4.00 Jan B FizzDrink 300 700 2.33 Feb B FizzDrink 200 250 1.25 Mar B FizzDrink 750 1,950 2.60 Total

select Brand, Product, Volume, Revenue,
AvgEarningPerProduct = Revenue * 1.0 / Volume,
Month
from
(
select Brand, Product,
Volume = case when Line = 'Volume' then Jan else 0 end,
Revenue = case when Line = 'Revenue' then Jan else 0 end,
Month = 'Jan'
from yourtable
union all
select Brand, Product,
Volume = case when Line = 'Volume' then Feb else 0 end,
Revenue = case when Line = 'Revenue' then Feb else 0 end,
Month = 'Feb'
from yourtable
union all
select Brand, Product,
Volume = case when Line = 'Volume' then Mar else 0 end,
Revenue = case when Line = 'Revenue' then Mar else 0 end,
Month = 'Mar'
from yourtable
)a|||

Hi Josh,

I'm not clear on having how you'd differentiate the multiple entries (I just added them together and recalculated average earnings):

Code Snippet

SELECT

Brand

, Product

, Volume

, Revenue

, Cast(Revenue*1./Volume As Decimal(5,2)) As AvgEarningPerProduct

, MonthName

FROM (

SELECT

Brand

, Product

, Sum(Case When Line = 'Volume' Then

Case MonthName

When 'jan' Then jan

When 'feb' Then feb

When 'mar' Then mar

When 'total' Then total

End

End

) Volume

, Sum(Case When line = 'Revenue' then

Case MonthName

When 'jan' Then jan

When 'feb' Then feb

When 'mar' Then mar

When 'total' Then total

End

End

) Revenue

, MonthName

, MonthNumber

FROM #data, (

SELECT 1 MonthNumber, 'Jan' MonthName UNION ALL

SELECT 2, 'Feb' UNION ALL

SELECT 3, 'Mar' UNION ALL

SELECT 13, 'Total') MonthData

GROUP BY Brand, Product, MonthName, MonthNumber

) Data

ORDER BY Brand, Product, MonthNumber;

Jamie

|||

Hi Tan,

Thanks for your help. The query now works perfect. The downside is performance. As I have 20 columns as the one i mentioned in example (Volume, Revenue etc.) and 12 months, it takes some time ro return the results.

BTW, there is aone small problem. Now when I pull the data in reports the month get sorted alphabeticall i.e. April onwards.

Any suggestion, how I can change that?

thanks a lot for your help

Regards

Josh

|||

Josh,

"BTW, there is aone small problem. Now when I pull the data in reports the month get sorted alphabeticall i.e. April onwards.

Any suggestion, how I can change that?"

Yes, create a table that has your Month abbreviations, and a SMALLINT or CHAR(1) sort_key to associate with each month. Include such a table in your JOINs to provide the desired sort order.

Dan