Conditional Count on a field

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 priority5from    Jobsgroup 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 Priority5FROM TableNameGROUP 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 Priority5FROM TableNameGROUP BY JobId, JobName