你可以试试下面的方法-

SELECT * FROM   
(
    SELECT Project,STATUS,count(*) AS cnt FROM Defect GROUP BY Project,STATUS
) t 
PIVOT(
    sum(cnt) 
    FOR STATUS IN (
        [Build Assigned], 
        [Build Delivered], 
        [Closed], 
        [Eng. Build Delivered], 
        [New], 
        [Non-Issue], 
        [Open],[Pending])
) AS pivot_table

会是

with base as
(
SELECT Project
    ,STATUS
    ,count(*) AS [count]
FROM Defect
GROUP BY Project
    ,STATUS
)
select *
from base
pivot (
sum([count])
for base.status in ([Build Delivered],[New],[...],[...])
) as x
order by project
;

可以使用聚合函数 sum 与 case 表达式如下

select
  project,
  sum(case when status = 'Build Assigned' then count else 0 end) as Build_Assigned,
  sum(case when status = 'Build Delivered' then count else 0 end) as Build_Delivered
from yourTable
group by
  project