Creating rows in a table based on columns from another
A PIVOT statement, given all its hard-coding, is a glorified CASE statement. It was easier to build out a UNION query with all the counters, and run CASE statements against the values.

You can definitely convert this into dynamic SQL for future reference, but this is a good start.

SELECT [AccountID],
    [SiteID],
    [ThreatName],
    SUM(ISNULL([08/2016], 0)) as [08/2016],
    SUM(ISNULL([09/2016], 0)) as [09/2016],
    SUM(ISNULL([10/2016], 0)) as [10/2016],
    SUM(ISNULL([08/2016], 0) + ISNULL([09/2016], 0) + ISNULL([10/2016], 0)) AS [Quarterly_Total]
     FROM (
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'IRA' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'RFI' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'XSS' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'SQLi' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'BDP' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
) a
GROUP BY [AccountID],
    [SiteID],
    [ThreatName]