Hi SO, i would like to aggregate all purchases for a certain product that used the same rebatecode (using SQL Server 2005)

Assume we have the following table:

ID        ProductID    Product  RebateCode      Amount
1         123          7HM      ABC            1
2         123          7HM      XYZ            2
3         124          7HM      ABC            10
4         124          7HM      XYZ            20
5         125          2EB      LOI            4
6         126          2EB      LOI            40

CREATE TABLE #ProductSales(ID SMALLINT, ProductID int, Product varchar(6), RebateCode varchar(4), Amount int)
GO
INSERT INTO #ProductSales
  select 1, 123, '7HM', 'A', 1 union all
  select 2, 123, '7HM', 'B', 2 union all
  select 3, 124, '7HM', 'A', 10 union all
  select 4, 124, '7HM', 'B', 20 union all
  select 5, 125, '7HM', 'A', 100 union all
  select 6, 125, '7HM', 'B', 200 union all
  select 7, 125, '7HM', 'C', 3 union all
  select 8, 126, '2EA', 'E', 4 union all
  select 8, 127, '2EA', 'E', 40 union all
  select 9, 128, '2EB', 'F', 5 union all
  select 9, 129, '2EB', 'F', 50 union all
  select 10, 130, '2EB', 'F', 500   
GO
SELECT * FROM #ProductSales
GO

/* And i would like to have the following result

Product nrOfProducts  CombinationRebateCode      SumAmount   ABC   LOI  XYZ
7HM     2             ABC, XYZ                    33          11     0   22
2EB     2             LOI                         44           0    44    0
..

*/

CREATE TABLE #ProductRebateCode(Product varchar(6), nrOfProducts int, sumAmountRebateCombo int, rebateCodeCombination varchar(80),  A int, B int, C int, E int, F int)
Go
 INSERT INTO #ProductRebateCode
  select '7HM', 2, 33, 'A, B', 2, 2, 0, 0, 0 union all
  select '7HM', 1, 303, 'A, B, C', 1, 1, 1, 0, 0 union all
  select '2EA', 2, 44, 'E', 0, 0, 0, 2, 0 union all
  select '2EB', 3, 555, 'E', 0, 0, 0, 0, 2 

Select * from #ProductRebateCode

-- Drop Table #ProductSales 
IF EXISTS (
 SELECT *
  FROM tempdb.dbo.sysobjects
  WHERE name LIKE '#ProductSales%')
  DROP TABLE #ProductSales 
-- Drop Table #ProductRebateCode 
IF EXISTS (
 SELECT *
  FROM tempdb.dbo.sysobjects
  WHERE name LIKE '#ProductRebateCode%')
  DROP TABLE #ProductRebateCode 

I would like to have the result like in the example (see second select (#ProductRebateCode).

I tried to achieve it with the crosstab from this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216&whichpage=6.

exec CrossTab2b
    @SQL  = 'SELECT [ProductID], Product, RebateCode, Amount FROM #ProductSales'
 ,@PivotCol = 'RebateCode'
 ,@Summaries = 'Sum(Amount ELSE 0)[_Sum], Count([ProductID])[_nrOfProducts]' /* SUM(Amount ELSE 0)[Amount], COUNT(Amount)[Qty]  */
 ,@GroupBy = 'RebateCode, Product'
 ,@OtherFields = 'Product' 

I believe that this could work, but i am unable to solve it.

Do you believe that it is possible to achieve what i am trying without MDX or the other fancy ?DX-Stuff?

Best regards And Thanks a lot debugger the other