SQL - Modify Query to Create Columns
I currently have this request. 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 reveal the severity in the columns. For example,
SiteName | Red | Yellow | Green | Orange
Orlando | 0 | 1 | 22 | 1321
+3
source to share
3 answers
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
it as it COUNT(NULL)
returns 0
anyway.
+1
source to share
The fact that you have it severity
in your own table allows it to go dynamically without much overhead.
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);
Demo version of the rexter: http://rextester.com/TYDFP90293
Created request:
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
+1
source to share
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
0
user7715598
source
to share