sql server - SQL - Modify Query to create Columns -

i have query. used work requirements.

 select sites.sitename,    severity.severity,    coalesce(count(vulns.id), 0) totals   sites    inner join systems            on sites.id = systems.siteid    cross join severity    left join vulns           on vulns.systemid = systems.id              , vulns.risk_factor = severity.severity group  sites.sitename,       severity.severity  

and 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 modify query break out severity columns. such

sitename | red | yellow | green | orange orlando  | 0   | 1      | 22    | 1321 

you can use conditional aggregation:

 select sites.sitename,            count(case when severity.severity = 'red' vulns.id end) red,         count(case when severity.severity = 'yellow' vulns.id end) yellow,         count(case when severity.severity = 'green' vulns.id end) green,         count(case when severity.severity = 'orange' vulns.id end) orange       sites    inner join systems            on sites.id = systems.siteid    cross join severity    left join vulns           on vulns.systemid = systems.id              , vulns.risk_factor = severity.severity group  sites.sitename 

note: don't need coalesce since count(null) returns 0 anyway.