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]

Solution to Month to month yearly results

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]

Solution

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:

[B]Year	Customer Name	Product Description	January	February	March	April	May	June	July	August	September	October	November	December[/B]
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!!!