WITH cteStatusCounts(FormsCount, SubjectID,StatusCodeID)
AS
(
SELECT COUNT(FormID) AS FormsCount, SubjectID,StatusCodeID
FROM tblFeedbackForms
Where SubjectAreaID=2
GROUP BY SubjectID,StatusCodeID
)
SELECT
FormsPivot.SubjectID as KeyID,
stTotalIncoming=ISNULL([1]+[2]+[3]+[4],0),
stOpen=ISNULL([1], 0),
stMoreInfo=ISNULL([2], 0),
stWithdrawn=ISNULL([3], 0),
stCoached=ISNULL([4], 0)
FROM
cteStatusCounts
PIVOT(sum(FormsCount) FOR StatusCodeID in([1],[2],[3],[4])) as FormsPivot
Order by FormsPivot.SubjectID
