Q:

Vertical total by group Pivot SQL

I have a Pivot query which counts employees by group per month on how many times they have logged in, I want to get a grand total per group, per month shown horizontally under the list of employees in the group.
Currently I can only get the totals to show as the overall total per group as an extra column
The SQL I am using is:
   SELECT
   Code,
   Full_Name,
   [1] AS Jan,
   [2] AS Feb,
   [3] AS Mar,
   [4] AS Apr,
   [5] AS May,
   [6] AS Jun,
   [7] AS Jul,
   [8] AS Aug,
   [9] AS Sep,
   [10] AS Oct,
   [11] AS Nov,
   [12] AS Dec,
   Total
   FROM 
   (SELECT Norgren_Group.Code
   , Full_Name
   , lh.Employee_Id
   , MONTH(lh.RN_Create_Date) [Month]
   , COUNT(lh.Employee_Id) OVER (PARTITION BY Norgren_Group_Id) as Total
   FROM Employee_Login_History lh
   JOIN Employee ON Employee.Employee_Id = lh.Employee_Id
   JOIN Norgren_Group ON Norgren_Group.Norgren_Group_Id = Employee.Default_Norgren_Group_Id
   WHERE Reporting_Region='Europe'
   AND YEAR(lh.RN_Create_Date) = YEAR(GETDATE())
   ) pvt
   PIVOT
   (
      COUNT(Employee_Id)
      FOR [Month]
      IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
   ) AS pvttble
   ORDER BY Code, Full_Name

A snippet of the result i get is:
 Code   Full_Name            Jan    Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
   A    Alexander Wipplinger 20     11  23  7   14  10  15  10  13  23  6   0   2894
   A    Andreas Grünanger    13     12  12  10  17  14  11  15  13  20  11  0   2894
   ALPE Albert Unger         16     18  28  9   13  17  18  19  17  22  18  0   16899
   ALPE Andreas Barz         1      0   0   0   0   0   0   0   0   0   0   0   16899

What I want to see is:
 Code   Full_Name            Jan    Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 
   A    Alexander Wipplinger 20     11  23  7   14  10  15  10  13  23  6   0   
   A    Andreas Grünanger    13     12  12  10  17  14  11  15  13  20  11  0   
        Total                33     23  35  17  31  24  26  25  26  43  17  0
   ALPE Albert Unger         16     18  28  9   13  17  18  19  17  22  18  0   
   ALPE Andreas Barz         1      0   0   0   0   0   0   0   0   0   0   0   
        Total                17     18  28  9   13  17  18  19  17  22  18  0

A:

One method is to switch to conditional aggregation and use GROUPING SETS:
SELECT ng.Code, Full_Name,
       SUM(CASE WHEN MONTH(lh.RN_Create_Date) = 1 THEN 1 ELSE 0 END) as Jan,
       SUM(CASE WHEN MONTH(lh.RN_Create_Date) = 2 THEN 1 ELSE 0 END) as Feb,
       . . .
       SUM(CASE WHEN MONTH(lh.RN_Create_Date) = 12 THEN 1 ELSE 0 END) as Dec
FROM Employee_Login_History lh JOIN
     Employee e
     ON e.Employee_Id = lh.Employee_Id JOIN
     Norgren_Group ng
     ON ng.Norgren_Group_Id = e.Default_Norgren_Group_Id
WHERE Reporting_Region = 'Europe'AND
      YEAR(lh.RN_Create_Date) = YEAR(GETDATE())
GROUP BY GROUPING SETS ( (ng.Code, Full_Name), () );

