473,390 Members | 1,049 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

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...

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

3 3065
nbiswas
149 100+
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
149 100+
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
Thank you so much! I appreciate it!!! Helped me a lot!!!
Nov 25 '09 #4

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

Similar topics

20
by: Laguna | last post by:
Hi Gurus, I want to find the expiration date of stock options (3rd Friday of the month) for an any give month and year. I have tried a few tricks with the functions provided by the built-in...
1
by: Wayfarer | last post by:
I've built a report using a aggregate query that is grouped by Month + Year, Program and Name. When I feed the date ranges as query parameters, it will very properly print out a report that shows...
4
by: J-P-W | last post by:
Hi, I have a system that records insurance policies. If the policy is cancelled then any part of a month insured is deducted from the premium, so a policy that ran for 32 days would get a 10...
4
by: crane.jake | last post by:
Hi, I'm trying to find the following information from the table found bellow. Year_Sales - # of sales that have occurred this year Year_Income - SUM(amount) Month_Sales - # of sales that have...
10
by: Jim | last post by:
I'm sure this has been asked before but I can't find any postings. I have a table that has weekly inspections for multiple buildings. What I need to do is break these down by the week of the...
2
by: DavidOwens | last post by:
SELECT DISTINCTROW Format(PERIOD.START_DT,"mmmm yyyy") AS MonthYear, SALES.PERIOD_ID, Sum(SALES.SALES) AS SumOfSALES, Sum(SALES.SALES_UNITS) AS SumOfSALES_UNITS, Sum(SALES.SALES_VALUE) AS...
2
by: rekhasc | last post by:
hi...... Its very urgent.............. how can i take only the year and month part and it should save in the database in the month and year field separately......... when i enter the date in...
25
by: rekhasc | last post by:
hi...... Its very urgent.............. how can i take only the year and month part and it should save in the database in the month and year field separately......... when i enter the date in the...
9
by: magickarle | last post by:
Hi, I have a database in access with the following columns: Day AgentID ManagerID Grade They got information about agents and some grades. I would like to have ONE form with several...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.