By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

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

antonopn
P: 42
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...

Expand|Select|Wrap|Line Numbers
  1. select customer.name, 
  2. product.descr, 
  3. (select sum(amount) from sales where... and month=JAN), 
  4. (select sum(amount) from sales where... and month=FEB), 
  5. (select sum(amount) from sales where... and month=MAR)... 
  6. 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?
Nov 23 '09 #1

✓ answered by nbiswas

First I am creating a sample records set

Expand|Select|Wrap|Line Numbers
  1. declare @t table(
  2.     [Year] int
  3.     ,[Customer Name] varchar(50)
  4.     ,[Product Description] varchar(50)
  5.     ,[Month] varchar(20)
  6.     ,[Amount] int)
  7. insert into @t
  8. select 2008,'Customer-1','Product-1','January',20081 union all
  9. select 2008,'Customer-1','Product-1','February',20082 union all
  10. select 2008,'Customer-1','Product-1','March',20083 union all
  11. select 2008,'Customer-1','Product-1','April',20084 union all
  12. select 2008,'Customer-1','Product-1','May',20085 union all
  13. select 2008,'Customer-1','Product-1','June',20086 union all
  14. select 2008,'Customer-1','Product-1','July',20087 union all
  15. select 2008,'Customer-1','Product-1','August',20088 union all
  16. select 2009,'Customer-1','Product-1','September',20089 union all
  17. select 2009,'Customer-1','Product-1','October',200810 union all
  18. select 2009,'Customer-1','Product-1','November',200811 union all
  19. select 2009,'Customer-1','Product-1','December',200812 union all
  20. select 2008,'Customer-4','Product-4','January',12008 union all
  21. select 2008,'Customer-4','Product-4','February',22008 union all
  22. select 2008,'Customer-4','Product-4','March',32008 union all
  23. select 2008,'Customer-4','Product-4','April',42008 union all
  24. select 2008,'Customer-4','Product-4','May',52008 union all
  25. select 2008,'Customer-4','Product-4','June',62008 union all
  26. select 2008,'Customer-4','Product-4','July',72008 union all
  27. select 2008,'Customer-4','Product-4','August',82008 union all
  28. select 2009,'Customer-4','Product-4','September',92008 union all
  29. select 2008,'Customer-4','Product-4','October',102008 union all
  30. select 2008,'Customer-4','Product-4','November',112008 union all
  31. select 2008,'Customer-4','Product-4','December',122008 union all
  32. select 2009,'Customer-1','Product-1','January',100 union all
  33. select 2009,'Customer-1','Product-1','February',200 union all
  34. select 2009,'Customer-1','Product-1','March',300 union all
  35. select 2009,'Customer-1','Product-1','April',400 union all
  36. select 2009,'Customer-1','Product-1','May',500 union all
  37. select 2009,'Customer-1','Product-1','June',600 union all
  38. select 2009,'Customer-1','Product-1','July',700 union all
  39. select 2009,'Customer-1','Product-1','August',800 union all
  40. select 2009,'Customer-1','Product-1','September',900 union all
  41. select 2009,'Customer-1','Product-1','October',1000 union all
  42. select 2009,'Customer-1','Product-1','November',1100 union all
  43. select 2009,'Customer-1','Product-1','December',1200 union all
  44. select 2009,'Customer-2','Product-2','January',10 union all
  45. select 2009,'Customer-2','Product-2','February',20 union all
  46. select 2009,'Customer-2','Product-2','March',30 union all
  47. select 2009,'Customer-2','Product-2','April',40 union all
  48. select 2009,'Customer-2','Product-2','May',50 union all
  49. select 2009,'Customer-2','Product-2','June',60 union all
  50. select 2009,'Customer-2','Product-2','July',70 union all
  51. select 2009,'Customer-2','Product-2','August',80 union all
  52. select 2009,'Customer-2','Product-2','September',90 union all
  53. select 2009,'Customer-2','Product-2','October',100 union all
  54. select 2009,'Customer-2','Product-2','November',110 union all
  55. select 2009,'Customer-2','Product-2','December',120 union all
  56. select 2009,'Customer-3','Product-3','January',30 union all
  57. select 2009,'Customer-3','Product-3','February',31 union all
  58. select 2009,'Customer-3','Product-3','March',32 union all
  59. select 2009,'Customer-3','Product-3','April',33 union all
  60. select 2009,'Customer-3','Product-3','May',34 union all
  61. select 2009,'Customer-3','Product-3','June',35 union all
  62. select 2009,'Customer-3','Product-3','July',36 union all
  63. select 2009,'Customer-3','Product-3','August',37 union all
  64. select 2009,'Customer-3','Product-3','September',38 union all
  65. select 2009,'Customer-3','Product-3','October',39 union all
  66. select 2009,'Customer-3','Product-3','November',40 union all
  67. select 2009,'Customer-3','Product-3','December',41 union all
  68. select 2009,'Customer-4','Product-4','January',1 union all
  69. select 2009,'Customer-4','Product-4','February',2 union all
  70. select 2009,'Customer-4','Product-4','March',3 union all
  71. select 2009,'Customer-4','Product-4','April',4 union all
  72. select 2009,'Customer-4','Product-4','May',5 union all
  73. select 2009,'Customer-4','Product-4','June',6  union all
  74. select 2010,'Customer-1','Product-1','January',11 union all
  75. select 2010,'Customer-1','Product-1','February',12 union all
  76. select 2010,'Customer-1','Product-1','March',13 union all
  77. select 2010,'Customer-1','Product-1','April',15 union all
  78. select 2010,'Customer-1','Product-1','May',15 union all
  79. select 2010,'Customer-1','Product-1','June',16 union all
  80. select 2010,'Customer-1','Product-1','July',17 union all
  81. select 2010,'Customer-1','Product-1','August',18 union all
  82. select 2010,'Customer-2','Product-2','October',21 union all
  83. select 2010,'Customer-2','Product-2','November',22 union all
  84. select 2010,'Customer-2','Product-2','December',23 union all
  85. select 2010,'Customer-3','Product-3','January',24 union all
  86. select 2010,'Customer-3','Product-3','February',25 union all
  87. select 2010,'Customer-3','Product-3','March',26 union all
  88. select 2010,'Customer-3','Product-3','April',27 union all
  89. select 2010,'Customer-3','Product-3','May',28 union all
  90. select 2010,'Customer-3','Product-3','December',29 union all
  91. select 2010,'Customer-4','Product-4','January',1000 union all
  92. select 2010,'Customer-4','Product-4','February',2000 union all
  93. select 2010,'Customer-4','Product-4','March',3000 union all
  94. select 2010,'Customer-4','Product-4','April',4000 union all
  95. select 2010,'Customer-4','Product-4','May',5000 union all
  96. select 2010,'Customer-4','Product-4','June',6000 union all
  97. select 2010,'Customer-4','Product-4','October',10000 union all
  98. select 2010,'Customer-4','Product-4','November',11000 union all
  99. select 2010,'Customer-4','Product-4','December',12000 
  100. select * from @t
Next I am executing the query(Pivoting)

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.         [Year]
  3.         ,[Customer Name]
  4.         ,[Product Description]        
  5.         ,[January],[February],[March],[April],[May],[June]
  6.         ,[July],[August],[September],[October],[November],[December]
  7. FROM    ( SELECT 
  8.                 [Year]
  9.                 ,[Customer Name]
  10.                 ,[Product Description]
  11.                 ,[Month]
  12.                  ,[Amount]
  13.           FROM      @t
  14.         ) p PIVOT ( SUM([Amount])
  15.                     FOR [Month] 
  16.                       IN (    [January],[February],[March],[April],[May],[June]
  17.                             ,[July],[August],[September],[October],[November],[December])
  18.                   ) AS pvt
Output:

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

Share this Question
Share on Google+
3 Replies


nbiswas
100+
P: 149
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.
(:
Nov 23 '09 #2

nbiswas
100+
P: 149
First I am creating a sample records set

Expand|Select|Wrap|Line Numbers
  1. declare @t table(
  2.     [Year] int
  3.     ,[Customer Name] varchar(50)
  4.     ,[Product Description] varchar(50)
  5.     ,[Month] varchar(20)
  6.     ,[Amount] int)
  7. insert into @t
  8. select 2008,'Customer-1','Product-1','January',20081 union all
  9. select 2008,'Customer-1','Product-1','February',20082 union all
  10. select 2008,'Customer-1','Product-1','March',20083 union all
  11. select 2008,'Customer-1','Product-1','April',20084 union all
  12. select 2008,'Customer-1','Product-1','May',20085 union all
  13. select 2008,'Customer-1','Product-1','June',20086 union all
  14. select 2008,'Customer-1','Product-1','July',20087 union all
  15. select 2008,'Customer-1','Product-1','August',20088 union all
  16. select 2009,'Customer-1','Product-1','September',20089 union all
  17. select 2009,'Customer-1','Product-1','October',200810 union all
  18. select 2009,'Customer-1','Product-1','November',200811 union all
  19. select 2009,'Customer-1','Product-1','December',200812 union all
  20. select 2008,'Customer-4','Product-4','January',12008 union all
  21. select 2008,'Customer-4','Product-4','February',22008 union all
  22. select 2008,'Customer-4','Product-4','March',32008 union all
  23. select 2008,'Customer-4','Product-4','April',42008 union all
  24. select 2008,'Customer-4','Product-4','May',52008 union all
  25. select 2008,'Customer-4','Product-4','June',62008 union all
  26. select 2008,'Customer-4','Product-4','July',72008 union all
  27. select 2008,'Customer-4','Product-4','August',82008 union all
  28. select 2009,'Customer-4','Product-4','September',92008 union all
  29. select 2008,'Customer-4','Product-4','October',102008 union all
  30. select 2008,'Customer-4','Product-4','November',112008 union all
  31. select 2008,'Customer-4','Product-4','December',122008 union all
  32. select 2009,'Customer-1','Product-1','January',100 union all
  33. select 2009,'Customer-1','Product-1','February',200 union all
  34. select 2009,'Customer-1','Product-1','March',300 union all
  35. select 2009,'Customer-1','Product-1','April',400 union all
  36. select 2009,'Customer-1','Product-1','May',500 union all
  37. select 2009,'Customer-1','Product-1','June',600 union all
  38. select 2009,'Customer-1','Product-1','July',700 union all
  39. select 2009,'Customer-1','Product-1','August',800 union all
  40. select 2009,'Customer-1','Product-1','September',900 union all
  41. select 2009,'Customer-1','Product-1','October',1000 union all
  42. select 2009,'Customer-1','Product-1','November',1100 union all
  43. select 2009,'Customer-1','Product-1','December',1200 union all
  44. select 2009,'Customer-2','Product-2','January',10 union all
  45. select 2009,'Customer-2','Product-2','February',20 union all
  46. select 2009,'Customer-2','Product-2','March',30 union all
  47. select 2009,'Customer-2','Product-2','April',40 union all
  48. select 2009,'Customer-2','Product-2','May',50 union all
  49. select 2009,'Customer-2','Product-2','June',60 union all
  50. select 2009,'Customer-2','Product-2','July',70 union all
  51. select 2009,'Customer-2','Product-2','August',80 union all
  52. select 2009,'Customer-2','Product-2','September',90 union all
  53. select 2009,'Customer-2','Product-2','October',100 union all
  54. select 2009,'Customer-2','Product-2','November',110 union all
  55. select 2009,'Customer-2','Product-2','December',120 union all
  56. select 2009,'Customer-3','Product-3','January',30 union all
  57. select 2009,'Customer-3','Product-3','February',31 union all
  58. select 2009,'Customer-3','Product-3','March',32 union all
  59. select 2009,'Customer-3','Product-3','April',33 union all
  60. select 2009,'Customer-3','Product-3','May',34 union all
  61. select 2009,'Customer-3','Product-3','June',35 union all
  62. select 2009,'Customer-3','Product-3','July',36 union all
  63. select 2009,'Customer-3','Product-3','August',37 union all
  64. select 2009,'Customer-3','Product-3','September',38 union all
  65. select 2009,'Customer-3','Product-3','October',39 union all
  66. select 2009,'Customer-3','Product-3','November',40 union all
  67. select 2009,'Customer-3','Product-3','December',41 union all
  68. select 2009,'Customer-4','Product-4','January',1 union all
  69. select 2009,'Customer-4','Product-4','February',2 union all
  70. select 2009,'Customer-4','Product-4','March',3 union all
  71. select 2009,'Customer-4','Product-4','April',4 union all
  72. select 2009,'Customer-4','Product-4','May',5 union all
  73. select 2009,'Customer-4','Product-4','June',6  union all
  74. select 2010,'Customer-1','Product-1','January',11 union all
  75. select 2010,'Customer-1','Product-1','February',12 union all
  76. select 2010,'Customer-1','Product-1','March',13 union all
  77. select 2010,'Customer-1','Product-1','April',15 union all
  78. select 2010,'Customer-1','Product-1','May',15 union all
  79. select 2010,'Customer-1','Product-1','June',16 union all
  80. select 2010,'Customer-1','Product-1','July',17 union all
  81. select 2010,'Customer-1','Product-1','August',18 union all
  82. select 2010,'Customer-2','Product-2','October',21 union all
  83. select 2010,'Customer-2','Product-2','November',22 union all
  84. select 2010,'Customer-2','Product-2','December',23 union all
  85. select 2010,'Customer-3','Product-3','January',24 union all
  86. select 2010,'Customer-3','Product-3','February',25 union all
  87. select 2010,'Customer-3','Product-3','March',26 union all
  88. select 2010,'Customer-3','Product-3','April',27 union all
  89. select 2010,'Customer-3','Product-3','May',28 union all
  90. select 2010,'Customer-3','Product-3','December',29 union all
  91. select 2010,'Customer-4','Product-4','January',1000 union all
  92. select 2010,'Customer-4','Product-4','February',2000 union all
  93. select 2010,'Customer-4','Product-4','March',3000 union all
  94. select 2010,'Customer-4','Product-4','April',4000 union all
  95. select 2010,'Customer-4','Product-4','May',5000 union all
  96. select 2010,'Customer-4','Product-4','June',6000 union all
  97. select 2010,'Customer-4','Product-4','October',10000 union all
  98. select 2010,'Customer-4','Product-4','November',11000 union all
  99. select 2010,'Customer-4','Product-4','December',12000 
  100. select * from @t
Next I am executing the query(Pivoting)

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.         [Year]
  3.         ,[Customer Name]
  4.         ,[Product Description]        
  5.         ,[January],[February],[March],[April],[May],[June]
  6.         ,[July],[August],[September],[October],[November],[December]
  7. FROM    ( SELECT 
  8.                 [Year]
  9.                 ,[Customer Name]
  10.                 ,[Product Description]
  11.                 ,[Month]
  12.                  ,[Amount]
  13.           FROM      @t
  14.         ) p PIVOT ( SUM([Amount])
  15.                     FOR [Month] 
  16.                       IN (    [January],[February],[March],[April],[May],[June]
  17.                             ,[July],[August],[September],[October],[November],[December])
  18.                   ) AS pvt
Output:

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

antonopn
P: 42
Thank you so much! I appreciate it!!! Helped me a lot!!!
Nov 25 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.