Month to month yearly results - hard and slow help...

[antonopn]

Hello to all!

I'm trying to make a very difficult report. I want to present sales of some products within an year but month-to-month.

lets say that the table would be like this...

customer|specie|quantityJAN|quantityFEB|quantityMAR...

But the problem is I need to make all these columns by hand and the query is like this...

select customer.name, 
product.descr, 
(select sum(amount) from sales where... and month=JAN), 
(select sum(amount) from sales where... and month=FEB), 
(select sum(amount) from sales where... and month=MAR)... 
etc, etc...

This query is REALLY slow. And finally I get an error of overpassing the limit of 256 tables...

Can you please help me?

[nbiswas]

Hi,
This kind of problems are called Cross Tab Queries and can be solved easily using PIVOTING (a feature from SQL SERVER 2005+).

Here is an example

Pivots with Dynamic Columns in SQL Server 2005

You can even look into

1) Cross-Tab reports in SQL Server 2005

2)Using PIVOT and UNPIVOT

Try with this. It is very simple.
If you cannot solve, then I will do that for you. Only give the correct inputs to me.
But try to get it done by yourself because you yourself will learn then.
(:

[nbiswas]

First I am creating a sample records set

declare @t table(
    [Year] int
    ,[Customer Name] varchar(50)
    ,[Product Description] varchar(50)
    ,[Month] varchar(20)
    ,[Amount] int)
insert into @t
select 2008,'Customer-1','Product-1','January',20081 union all
select 2008,'Customer-1','Product-1','February',20082 union all
select 2008,'Customer-1','Product-1','March',20083 union all
select 2008,'Customer-1','Product-1','April',20084 union all
select 2008,'Customer-1','Product-1','May',20085 union all
select 2008,'Customer-1','Product-1','June',20086 union all
select 2008,'Customer-1','Product-1','July',20087 union all
select 2008,'Customer-1','Product-1','August',20088 union all
select 2009,'Customer-1','Product-1','September',20089 union all
select 2009,'Customer-1','Product-1','October',200810 union all
select 2009,'Customer-1','Product-1','November',200811 union all
select 2009,'Customer-1','Product-1','December',200812 union all
select 2008,'Customer-4','Product-4','January',12008 union all
select 2008,'Customer-4','Product-4','February',22008 union all
select 2008,'Customer-4','Product-4','March',32008 union all
select 2008,'Customer-4','Product-4','April',42008 union all
select 2008,'Customer-4','Product-4','May',52008 union all
select 2008,'Customer-4','Product-4','June',62008 union all
select 2008,'Customer-4','Product-4','July',72008 union all
select 2008,'Customer-4','Product-4','August',82008 union all
select 2009,'Customer-4','Product-4','September',92008 union all
select 2008,'Customer-4','Product-4','October',102008 union all
select 2008,'Customer-4','Product-4','November',112008 union all
select 2008,'Customer-4','Product-4','December',122008 union all
select 2009,'Customer-1','Product-1','January',100 union all
select 2009,'Customer-1','Product-1','February',200 union all
select 2009,'Customer-1','Product-1','March',300 union all
select 2009,'Customer-1','Product-1','April',400 union all
select 2009,'Customer-1','Product-1','May',500 union all
select 2009,'Customer-1','Product-1','June',600 union all
select 2009,'Customer-1','Product-1','July',700 union all
select 2009,'Customer-1','Product-1','August',800 union all
select 2009,'Customer-1','Product-1','September',900 union all
select 2009,'Customer-1','Product-1','October',1000 union all
select 2009,'Customer-1','Product-1','November',1100 union all
select 2009,'Customer-1','Product-1','December',1200 union all
select 2009,'Customer-2','Product-2','January',10 union all
select 2009,'Customer-2','Product-2','February',20 union all
select 2009,'Customer-2','Product-2','March',30 union all
select 2009,'Customer-2','Product-2','April',40 union all
select 2009,'Customer-2','Product-2','May',50 union all
select 2009,'Customer-2','Product-2','June',60 union all
select 2009,'Customer-2','Product-2','July',70 union all
select 2009,'Customer-2','Product-2','August',80 union all
select 2009,'Customer-2','Product-2','September',90 union all
select 2009,'Customer-2','Product-2','October',100 union all
select 2009,'Customer-2','Product-2','November',110 union all
select 2009,'Customer-2','Product-2','December',120 union all
select 2009,'Customer-3','Product-3','January',30 union all
select 2009,'Customer-3','Product-3','February',31 union all
select 2009,'Customer-3','Product-3','March',32 union all
select 2009,'Customer-3','Product-3','April',33 union all
select 2009,'Customer-3','Product-3','May',34 union all
select 2009,'Customer-3','Product-3','June',35 union all
select 2009,'Customer-3','Product-3','July',36 union all
select 2009,'Customer-3','Product-3','August',37 union all
select 2009,'Customer-3','Product-3','September',38 union all
select 2009,'Customer-3','Product-3','October',39 union all
select 2009,'Customer-3','Product-3','November',40 union all
select 2009,'Customer-3','Product-3','December',41 union all
select 2009,'Customer-4','Product-4','January',1 union all
select 2009,'Customer-4','Product-4','February',2 union all
select 2009,'Customer-4','Product-4','March',3 union all
select 2009,'Customer-4','Product-4','April',4 union all
select 2009,'Customer-4','Product-4','May',5 union all
select 2009,'Customer-4','Product-4','June',6  union all
select 2010,'Customer-1','Product-1','January',11 union all
select 2010,'Customer-1','Product-1','February',12 union all
select 2010,'Customer-1','Product-1','March',13 union all
select 2010,'Customer-1','Product-1','April',15 union all
select 2010,'Customer-1','Product-1','May',15 union all
select 2010,'Customer-1','Product-1','June',16 union all
select 2010,'Customer-1','Product-1','July',17 union all
select 2010,'Customer-1','Product-1','August',18 union all
select 2010,'Customer-2','Product-2','October',21 union all
select 2010,'Customer-2','Product-2','November',22 union all
select 2010,'Customer-2','Product-2','December',23 union all
select 2010,'Customer-3','Product-3','January',24 union all
select 2010,'Customer-3','Product-3','February',25 union all
select 2010,'Customer-3','Product-3','March',26 union all
select 2010,'Customer-3','Product-3','April',27 union all
select 2010,'Customer-3','Product-3','May',28 union all
select 2010,'Customer-3','Product-3','December',29 union all
select 2010,'Customer-4','Product-4','January',1000 union all
select 2010,'Customer-4','Product-4','February',2000 union all
select 2010,'Customer-4','Product-4','March',3000 union all
select 2010,'Customer-4','Product-4','April',4000 union all
select 2010,'Customer-4','Product-4','May',5000 union all
select 2010,'Customer-4','Product-4','June',6000 union all
select 2010,'Customer-4','Product-4','October',10000 union all
select 2010,'Customer-4','Product-4','November',11000 union all
select 2010,'Customer-4','Product-4','December',12000 
select * from @t

Next I am executing the query(Pivoting)

SELECT 
        [Year]
        ,[Customer Name]
        ,[Product Description]        
        ,[January],[February],[March],[April],[May],[June]
        ,[July],[August],[September],[October],[November],[December]
FROM    ( SELECT 
                [Year]
                ,[Customer Name]
                ,[Product Description]
                ,[Month]
                 ,[Amount]
          FROM      @t
        ) p PIVOT ( SUM([Amount])
                    FOR [Month] 
                      IN (    [January],[February],[March],[April],[May],[June]
                            ,[July],[August],[September],[October],[November],[December])
                  ) AS pvt

Output:

Year    Customer Name    Product Description    January    February    March    April    May    June    July    August    September    October    November    December
2008    Customer-1    Product-1    20081    20082    20083    20084    20085    20086    20087    20088    NULL    NULL    NULL    NULL
2008    Customer-4    Product-4    12008    22008    32008    42008    52008    62008    72008    82008    NULL    102008    112008    122008
2009    Customer-1    Product-1    100    200    300    400    500    600    700    800    20989    201810    201911    202012
2009    Customer-2    Product-2    10    20    30    40    50    60    70    80    90    100    110    120
2009    Customer-3    Product-3    30    31    32    33    34    35    36    37    38    39    40    41
2009    Customer-4    Product-4    1    2    3    4    5    6    NULL    NULL    92008    NULL    NULL    NULL
2010    Customer-1    Product-1    11    12    13    15    15    16    17    18    NULL    NULL    NULL    NULL
2010    Customer-2    Product-2    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    21    22    23
2010    Customer-3    Product-3    24    25    26    27    28    NULL    NULL    NULL    NULL    NULL    NULL    29
2010    Customer-4    Product-4    1000    2000    3000    4000    5000    6000    NULL    NULL    NULL    10000    11000    12000

Hope this helps

[antonopn]

Thank you so much! I appreciate it!!! Helped me a lot!!!