SQL Aggregate all Purchases for a certain product with same rebatecode

Question

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         7HM     ABC             100
6   125         7HM     XYZ             200
7   125         7HM     CEX             3
8   126         2EA     ECU             4
8   127         2EA     ECU             40
9   128         2EB     FBA             5
9   129         2EB     FBA             50
10  130         2EB     FBA             500

This can be created with the following SQL.

CREATE TABLE #ProductSales(ID SMALLINT, ProductID int, Product varchar(6), RebateCode varchar(4), Amount int)
GO
INSERT INTO #ProductSales
  select 1, 123, '7HM', 'ABC', 1 union all
  select 2, 123, '7HM', 'XYZ', 2 union all
  select 3, 124, '7HM', 'ABC', 10 union all
  select 4, 124, '7HM', 'XYZ', 20 union all
  select 5, 125, '7HM', 'ABC', 100 union all
  select 6, 125, '7HM', 'XYZ', 200 union all
  select 7, 125, '7HM', 'CEX', 3 union all
  select 8, 126, '2EA', 'ECU', 4 union all
  select 8, 127, '2EA', 'ECU', 40 union all
  select 9, 128, '2EB', 'FBA', 5 union all
  select 9, 129, '2EB', 'FBA', 50 union all
  select 10, 130,'2EB', 'FBA', 500
GO

And I would like to have the following result. As you can see since 7HM was used in 2 different combinations (2 times ABC + XYZ) and 1 time (ABC + CEX + XYZ) it was split into two rows:

Product nrOfProducts  rebateCodeCombination       SumAmount   ABC  CEX  ECU  FBA  XYZ
7HM     2             ABC, XYZ                    33          11    0    0    0    22
7HM     1             ABC, CEX, XYZ               303         100   3    0    0   200
2EB     3             FBA                         555          0    0    0  555     0
2EA     2             ECU                         44           0    0   44    0     0
..

The suggested solution from Thomas (see below) is already pretty close.

I changed Thomas' solution just a tiny bit to reflect my example changes and to distinct on productID.

My minor changes to Thomas' Solution

Select PS.Product
    , Count(Distinct ProductID) As NrOfProducts
    , Stuff(
            (
                Select ', ' + PS1.RebateCode
                From #ProductSales As PS1
                Where PS1.Product = PS.Product
                Order By PS1.RebateCode
                For Xml Path('')
            ), 1, 2, '') As ComboRebateCode
    , Sum(PS.Amount) As Amount
    , Sum( Case When PS.RebateCode = 'ABC' Then PS.Amount End ) As [ABC]
    , Sum( Case When PS.RebateCode = 'CEX' Then PS.Amount End ) As [CEX]
    , Sum( Case When PS.RebateCode = 'ECU' Then PS.Amount End ) As [ECU]
    , Sum( Case When PS.RebateCode = 'FBA' Then PS.Amount End ) As [FBA]
    , Sum( Case When PS.RebateCode = 'XYZ' Then PS.Amount End ) As [XYZ]
From #ProductSales As PS
Group By PS.Product

Thomas' Solution (with my minor changes) gives this result

Product NrOfPrd  ComboRebateCode    Amount  ABC     CEX    ECU  FBA    XYZ
7HM         3     ABC,[...], XYZ    336     111     3      NULL NULL   222
2EB         3     FBA, FBA, FBA     555     NULL    NULL   NULL 555    NULL
2EA         2     ECU, ECU           44     NULL    NULL    44  NULL   NULL

This is already great there are just 2 things:

7HM 3 ABC, ABC, ABC, CEX, XYZ, XYZ, XYZ ...
                                              instead of 2 results
7HM 2 ABC, XYZ ...
7HM 1 ABC, CEX, XYZ,

And currently it concatenates every rebate code:

2EB 3 FBA, FBA, FBA
7HM 2 ABC, ABC, XYZ, XYZ
                                                    instead of
2EB 3 FBA
7HM 2 ABC, XYZ

Do you know how and where to make the changes to achieve the result (only identical rebate codes combos for a certain product are aggregated (2 7HM and 1 7HM), rebate code combination without repeating the rebate code (so "ABC, XYZ" instead of "ABC, ABC, XYZ, XYZ") and if possible 0 instead of NULL?

Just to clean up (remove temp tables):

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

Answer 1

Assuming that rebate codes are fixed in number, you can do something like:

Select PS.Product
    , Count(PS.ProductId) As NrOfProducts
    , Stuff(
            (
            Select ', ' + PS1.RebateCode
            From #ProductSales As PS1
            Where PS1.Product = PS.Product
            Order By PS1.RebateCode
            For Xml Path('')
            ), 1, 2, '') As CombinationRebateCode
    , Sum(PS.Amount) As Amount
    , Sum( Case When PS.RebateCode = 'ABC' Then PS.Amount End ) As [ABC]
    , Sum( Case When PS.RebateCode = 'LOI' Then PS.Amount End ) As [LOI]
    , Sum( Case When PS.RebateCode = 'XYZ' Then PS.Amount End ) As [XYZ]
From #ProductSales As PS
Group By PS.Product

If the number of rebate codes can variable, then the only way to accomplish that is with some fugly dynamic SQL. If you need dynamic rebate columns, then I would build that resultset in the middle-tier or client code or using a reporting tool.

Answer 2

SQL has a pivot command that's not that well known. Should be something like:

select *
from #ProductSales
pivot
(
  sum(Amount)
  for RebateCode in ([ABC],[XYZ],[LOI])
)
as p

Answer 3

If the number of rebate codes is below a certain maximum, then you can write a PIVOT query to turn your rows into columns. If there is no maximum, and you don't mind dynamic SQL, then you can dynamically generate the query (sill based on a pivot.) Both of these approaches are detailed in this article - Pivots with Dynamic SQL