Complex aggregration view how to

Question

Complex aggregration view how to

I have a table ( Table A ) with [payment type],[Agent],[Amount Credit] and [Amount Debit]. Now I am looking for a particular view of this data .

I want for EACH agent a summary of his/her activity.

So Agent along the x axis with Payment type along the y axis with Totals of each Agent ther as well .

Transaction type      Agent 1   Agent 2 

Amount Credit                 

Cash                20   40
Credit Card         20   20 

Total               40   60

Amount Debit                

Cash                20   40
Credit Card         10   10 

Total               30   50 

Tried everything and cant get this view yet .

Answer 1

You can get the result that you want by applying both the UNPIVOT and the PIVOT functions. If you have a known number of agent values that you want to convert into columns, then you can hard-code the query:

select 
  case when TransactionType is null then 'Total' else [Credit/Debit] end [Credit/Debit],
  case when TransactionType is null then '' else TransactionType end TransactionType,
  Sum([Agent 1]) Agent1, 
  sum([Agent 2]) Agent2
from
(
  select  [Agent], 
    [Credit/Debit], 
    PaymentType as TransactionType, 
    value
  from TableA
  unpivot
  (
    value
    for [Credit/Debit] in ([AmountCredit], [AmountDebit])
  ) unpiv
) src
pivot
(
  sum(value)
  for agent in ([Agent 1], [Agent 2])
) piv
group by GROUPING SETS ([Credit/Debit], TransactionType), ([Credit/Debit]);

See SQL Fiddle with Demo.

If you have an unknown number of agents then you will need to use dynamic SQL but you cannot use dynamic SQL in a view, you will have to place the code in a stored procedure. The dynamic SQL will be:

DECLARE @cols AS NVARCHAR(MAX),
    @colSum AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Agent) 
                    from TableA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colSum = STUFF((SELECT distinct ', Sum(' + QUOTENAME(Agent)+') as ' +QUOTENAME(Agent)
                    from TableA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'select 
                case when TransactionType is null then ''Total'' else [Credit/Debit] end [Credit/Debit],
                case when TransactionType is null then '''' else TransactionType end TransactionType,
                '+@colSum +'
              from
              (
                select  [Agent], 
                  [Credit/Debit], 
                  PaymentType as TransactionType, 
                  value
                from TableA
                unpivot
                (
                  value
                  for [Credit/Debit] in ([AmountCredit], [AmountDebit])
                ) unpiv
              ) src
              pivot
              (
                sum(value)
                for agent in ('+@cols+')
              ) piv
              group by GROUPING SETS ([Credit/Debit], TransactionType), ([Credit/Debit])'

execute(@query)

See SQL Fiddle with Demo. The result of the query will be:

| CREDIT/DEBIT | TRANSACTIONTYPE | AGENT 1 | AGENT 2 |
------------------------------------------------------
| AmountCredit |            Cash |      20 |      40 |
| AmountCredit |     Credit Card |      20 |      20 |
|        Total |                 |      40 |      60 |
|  AmountDebit |            Cash |      20 |      40 |
|  AmountDebit |     Credit Card |      10 |      10 |
|        Total |                 |      30 |      50 |