CODEWITHSUNDEEP
sqlsql-server
Zach Schulze
Zach Schulze

Reputation: 314

SQL - Modify Query to create Columns

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

Upvotes: 3

Views: 62

Answers (3)

user7715598
user7715598

Reputation: 

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

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

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

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

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.

Upvotes: 1

Related Questions