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>
How the resultset should look like>
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
No comments:
Post a Comment