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?