0

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.

0

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]
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.