Sql-server – Creating rows in a table based on columns from another

pivotsql-server-2012

I have an MS SQL 2012 DB with a table in the following format:

account_id   site_id   IRA   RFI   XSS   SQLi   BDP   Month
32501        112233    1     4     4     3      0     August,2016
32501        112233    5     2     6     1      0     September,2016
32501        110025    3     1     5     3      0     September,2016
32501        112233    4     5     2     7      0     October,2016

The table may have multiple lines per account (as the number of sites it contains) as well as multiple accounts. At most each site may have 3 lines, one for each month in a 3-months period.

I'm looking for a way to create a table that is based on the one above but in the following format:

AccountID    SiteID    ThreatName    08/2016    09/2016    10/2016    Quarterly_Total
32501        112233    IRA            1            5        4            10
32501        112233    RFI            4            2        5            11
32501        112233    XSS            4            6        2            12
32501        112233    SQLi           3            3        7            13
32501        112233    BDP            0            0        0            0
32501        110025    XSS            0            3        0            3
[...]

Basically it's a matter of making columns into rows from the source table and I've read somewhere that there is a PIVOT function that can help but didn't really understood how to use its syntax in my case. Also I should point out that the source table is actually a view that I create based on several different tables using a stored procedure.

Best Answer

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]
Related Question