Another solution is to use
Pivot[
^]:
SELECT mcdesp, [GOOD], [URWP], [URWT]
FROM (
SELECT mcdesp,mcopsts
FROM machine) AS DT
PIVOT (COUNT(mcopsts) FOR mcdesp IN([GOOD], [URWP], [URWT])) AS PT
If you would like to use dynamic pivots, see these:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/87582c2b-2f4a-4be3-a1a7-3bc8235d4c7f/[
^]
http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/[
^]
To get total:
SELECT mcdesp, [GOOD], [URWP], [URWT], [GOOD] + [URWP] + [URWT] AS Total
FROM(
SELECT mcdesp, [GOOD], [URWP], [URWT]
FROM (
SELECT mcdesp,mcopsts
FROM machine) AS DT
PIVOT (COUNT(mcopsts) FOR mcdesp IN([GOOD], [URWP], [URWT])) AS PT) AS T