Conditional Count on a field

If I had a table like this:

jobId, jobName, Priority

Whereby Priority can be an integer between 1 to 5.

Since I would need this query for generating a chart on report, I would need to display the jobid, jobname and 5 fields called Priority1, Priority2, Priority3, Priority4. Priority5.

Priority1 should count the amount of rows where priority field has the value of 1.

Priority2 should count the amount of rows where priority field has the value of 2.

Priority3 should count the amount of rows where priority field has the value of 3.

etc

How would I do that in a quick and performant manner?

I think you may be after

select 
    jobID, JobName,
    sum(case when Priority = 1 then 1 else 0 end) as priority1,
    sum(case when Priority = 2 then 1 else 0 end) as priority2,
    sum(case when Priority = 3 then 1 else 0 end) as priority3,
    sum(case when Priority = 4 then 1 else 0 end) as priority4,
    sum(case when Priority = 5 then 1 else 0 end) as priority5
from
    Jobs
group by 
    jobID, JobName

However I am uncertain if you need to the jobID and JobName in your results if so remove them and remove the group by,

Using COUNT instead of SUM removes the requirement for an ELSE statement:

SELECT jobId, jobName,
    COUNT(CASE WHEN Priority=1 THEN 1 END) AS Priority1,
    COUNT(CASE WHEN Priority=2 THEN 1 END) AS Priority2,
    COUNT(CASE WHEN Priority=3 THEN 1 END) AS Priority3,
    COUNT(CASE WHEN Priority=4 THEN 1 END) AS Priority4,
    COUNT(CASE WHEN Priority=5 THEN 1 END) AS Priority5
FROM TableName
GROUP BY jobId, jobName

IIF is not a standard SQL construct, but if it's supported by your database, you can achieve a more elegant statement producing the same result:

SELECT JobId, JobName,

COUNT(IIF (Priority=1, 1, NULL)) AS Priority1,
COUNT(IIF (Priority=2, 1, NULL)) AS Priority2,
COUNT(IIF (Priority=3, 1, NULL)) AS Priority3,
COUNT(IIF (Priority=4, 1, NULL)) AS Priority4,
COUNT(IIF (Priority=5, 1, NULL)) AS Priority5

FROM TableName
GROUP BY JobId, JobName