SQL Aggregate all Purchases for a certain product with same rebatecode
- by debuggerlikeanother
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
