Grouping in a SQL Pivot

Question

I've managed to create a pivot table in MS SQL and I'd like to group the annual columns by the GrpID with rollup. I understand as much that there's an aggregate clause which should be adhered to, but can't get my head around it.

SELECT * 

FROM(

    SELECT Descr, NameID, grpid,YE,total from appendicesgrouping
) as t

PIVOT (sum(total) 
       for ye in (
                  [2010],[2011],[2012],[2013],[2014]
                  )
       ) as PVT 

WHERE grpid < 7 
    AND practiceiD = 2 

ORDER BY nameid asc, grpid asc, descr asc

This gives the following return:

Descr|NameID|GrpID|2010|2011|2012|2013|2014|

Sale1|1     |1    |1000|1100|900 |1050|1125|

Sale2|1     |1    |500 |600 |650 |550 |525 |

Sale3|1     |1    |45  |50  |50  |45  |45  | 

Cost1|1     |2    |10  |10  |10  |10  |10  | 

Cost2|1     |2    |5   |5   |5   |5   |5   | 

Cost10|1     |3    |10  |10  |10  |10  |10  | 

Cost11|1     |3    |5   |5   |5   |5   |5   |

Ideally what I'd be after under 'Sale3' in each column would be 1545|1750|1645|1695 and so on for each change of GrpID.

Answer 1

Try this. Use SUM aggregate in select and Add GROUP BY grpid,NameID,Descr WITH rollup after the Where Clause

SELECT Descr,
       NameID,
       grpid,
       Sum([2010])[2010],
       Sum([2011])[2011],
       Sum([2012])[2012],
       Sum([2013])[2013],
       Sum([2014])[2014]
FROM  (SELECT Descr,
              NameID,
              grpid,
              YE,
              total
       FROM   appendicesgrouping) AS t
      PIVOT (Sum(total)
            FOR ye IN ([2010],[2011],[2012],[2013],[2014])) AS PVT
WHERE  grpid < 7
       AND practiceiD = 2
GROUP  BY grpid,
          NameID,
          Descr WITH rollup
ORDER  BY nameid ASC,
          grpid ASC,
          descr ASC