SQL Pivot on Conditional Count

I have a table of vulnerabilities using SQL server, when I perform the following query

select * from table 

The output looks like so.

| Name          | HostName   | Week   |
| ------------- |------------| -------|
| java          | Hosta      |    1   |
| java          | Hostb      |    1   |
| java          | Hostb      |    2   |
| Ansible       | Hosta      |    1   |
| Ansible       | Hosta      |    2   |
| Ansible       | Hosta      |    3   |
| Ansible       | Hostb      |    3   |

My aim is to generate an output that pivots the weeks into column tables, with the values being a count of Hosts for a given vulnerability in that week.

| Vulnerability | Week 1 | Week 2 | Week 3 |
| ------------- |--------| -------| -------|
| java          |    2   |    1   |    0   |
| Ansible       |    1   |    1   |    2   |

My initial attempt was to do

select * from table 
PIVOT(
count(HostName)
For week in ([1],[2],[3])
) AS OUT

But the output was the correct layout, but incorrect data as if it was only counting the first occurrence. Is an amendment to the count term required or is my approach the wrong one?

Answer

Conditional aggregation is simpler:

select vulnerability,
       sum(case when week = 1 then 1 else 0 end) as week_1,
       sum(case when week = 2 then 1 else 0 end) as week_2,
       sum(case when week = 3 then 1 else 0 end) as week_3
from t
group by vulnerability;

Note only is pivot bespoke syntax, but it is sensitive to what columns are in the table. Extra columns are interpreted as “group by” criteria, affecting the results from the query.