SQL - Modify Query to create Columns

Question

I currently have this query. Which used to work for my requirements.

 SELECT sites.sitename,
   severity.severity,
   COALESCE(Count(vulns.id), 0) AS Totals
FROM   sites
   INNER JOIN systems
           ON sites.id = systems.siteid
   CROSS JOIN severity
   LEFT JOIN vulns
          ON vulns.systemid = systems.id
             AND vulns.risk_factor = severity.severity
GROUP  BY sites.sitename,
      severity.severity

And this returns results like

SiteName | Severity | Totals
Orlando  | Red      | 0
Orlando  | Yellow   | 1
Orlando  | Green    | 22
Orlando  | Orange   | 1321
Tampa    | Red      | 22
Tampa    | Yellow   | 111
Tampa    | Green    | 223
Tampa    | Orange   | 121

How can I modify this query to break out the severity into columns. Such as

SiteName | Red | Yellow | Green | Orange
Orlando  | 0   | 1      | 22    | 1321

are the number of severities limited? or do you have many others? — Vamsi Prabhala
– Vamsi Prabhala, Commented Apr 7, 2017 at 12:54
Many others. Not limited at all. I am looking into pivot right now — Zach Schulze
– Zach Schulze, Commented Apr 7, 2017 at 12:54

Giorgos Betsos · Accepted Answer · 2017-04-07 12:55:32Z

1

You can use conditional aggregation:

 SELECT sites.sitename,   
        Count(CASE WHEN severity.severity = 'Red' THEN vulns.id END) AS Red,
        Count(CASE WHEN severity.severity = 'Yellow' THEN vulns.id END) AS Yellow,
        Count(CASE WHEN severity.severity = 'Green' THEN vulns.id END) AS Green,
        Count(CASE WHEN severity.severity = 'Orange' THEN vulns.id END) AS Orange    
FROM   sites
   INNER JOIN systems
           ON sites.id = systems.siteid
   CROSS JOIN severity
   LEFT JOIN vulns
          ON vulns.systemid = systems.id
             AND vulns.risk_factor = severity.severity
GROUP  BY sites.sitename

Note: You don't need COALESCE since COUNT(NULL) returns 0 anyway.

answered Apr 7, 2017 at 12:55

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zach Schulze Over a year ago

Thank you very much. And also thanks for the note on COALESCE and COUNT

SqlZim · Accepted Answer · 2017-04-07 13:06:36Z

The fact that you have severity in its own table enables this to go dynamic without much overhead at all.

Using dynamic conditional aggregation:

create table severity (severity varchar(32));
insert into severity values ('Red'),('Yellow'),('Green'),('Orange');

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
select @cols = stuff((
    select distinct 
      char(10)+'      , ' 
      + quotename(se.severity)
      +' = sum(case when se.severity = '''+se.severity+''' then 1 else 0 end)'
    from severity se
    order by 1
    for xml path (''), type).value('.','nvarchar(max)')
  ,1,0,'')
select  @sql ='
select 
    si.sitename'+@cols+'
FROM   sites si
   INNER JOIN systems sy
           ON si.id = sy.siteid
   CROSS JOIN severity se
   LEFT JOIN vulns
          ON vulns.systemid = systems.id
             AND vulns.risk_factor = severity.severity
GROUP  BY si.sitename
group by Id'
select CodeGenerated = @sql
--exec(@sql);

rextester demo: http://rextester.com/TYDFP90293

Query Generated:

select 
    si.sitename
  , [Green] = sum(case when se.severity = 'Green' then 1 else 0 end)
  , [Orange] = sum(case when se.severity = 'Orange' then 1 else 0 end)
  , [Red] = sum(case when se.severity = 'Red' then 1 else 0 end)
  , [Yellow] = sum(case when se.severity = 'Yellow' then 1 else 0 end)
FROM   sites si
   INNER JOIN systems sy
           ON si.id = sy.siteid
   CROSS JOIN severity se
   LEFT JOIN vulns
          ON vulns.systemid = systems.id
             AND vulns.risk_factor = severity.severity
GROUP  BY si.sitename
group by Id

Very interesting. Thanks. I will definitely look into this as well

user7715598 · Accepted Answer · 2017-04-07 13:22:02Z

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

;WITH cte1(SiteName , Severity , Totals)
AS
(
select 'Orlando'  , 'Red'      , 0      Union all
select 'Orlando'  , 'Yellow'   , 1      Union all
select 'Orlando'  , 'Green'    , 22     Union all
select 'Orlando'  , 'Orange'   , 1321   Union all
select 'Tampa'    , 'Red'      , 22     Union all
select 'Tampa'    , 'Yellow'   , 111    Union all
select 'Tampa'    , 'Green'    , 223    Union all
select 'Tampa'    , 'Orange'   , 121
)
SELECT *INTO #temp FROM cte1

SELECT SiteName,
       MAX(CASE WHEN Severity = 'Red' THEN Totals END) Red,
       MAX(CASE WHEN Severity = 'Yellow' THEN Totals END) Yellow,
       MAX(CASE WHEN Severity = 'Green' THEN Totals END) Green,
       MAX(CASE WHEN Severity = 'Orange' THEN Totals END) Orange

  FROM  #temp
 GROUP BY SiteName

Collectives™ on Stack Overflow

SQL - Modify Query to create Columns

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related