DECLARE @Table TABLE(nbr VARCHAR(12), [year] INT, [role] VARCHAR(3), status VARCHAR(3), [count] INT)
 
INSERT @Table
SELECT '90-04424-01', 2006, 'CMT', 'OFF', 20 
UNION SELECT '90-04424-01', 2006, 'CMT', 'ON', 47
UNION SELECT '90-04424-01', 2006, 'HOS', 'OFF', 3 
UNION SELECT '90-04424-01', 2006, 'HOS', 'ON', 10 
UNION SELECT '90-04424-01', 2006, 'MCP', 'OFF', 1 
UNION SELECT '90-04424-01', 2006, 'P1', 'OFF', 1 
UNION SELECT '90-04424-01', 2006, 'P1', 'ON', 5 
UNION SELECT '90-04424-01', 2006, 'PIN', 'OFF', 8 
UNION SELECT '90-04424-01', 2006, 'PIN', 'ON', 58 
UNION SELECT '90-04424-04', 2005, 'CMT', 'ON', 1 
UNION SELECT '90-04424-04', 2005, 'PIN', 'ON', 1 
UNION SELECT '90-04424-04', 2006, 'CMT', 'ON', 8 
UNION SELECT '90-04424-04', 2006, 'PIN', 'ON', 9 
UNION SELECT '90-04424-08', 2006, 'CMT', 'ON', 1 
UNION SELECT '90-04424-08', 2006, 'PIN', 'ON', 1 
;

SELECT 
	nbr,
	[year],
	SUM(
		CASE 
			WHEN [role] = 'PIN' AND status = 'ON' THEN [count]
			ELSE 0
		END
	) AS IsOn,
	SUM(
		CASE 
			WHEN [role] = 'PIN' AND status = 'OFF' THEN [count]
			ELSE 0
		END
	) AS IsOff,
	SUM(
		CASE 
			WHEN [role] = 'CMT' AND status = 'ON' THEN [count]
			ELSE 0
		END
	) AS CmtOn,
	SUM(
		CASE 
			WHEN [role] = 'CMT' AND status = 'OFF' THEN [count]
			ELSE 0
		END
	) AS CmtOff,
	SUM(
		CASE 
			WHEN ([role] = 'P1' OR [role] = 'MCP' OR [role] = 'HOS') AND status = 'ON' THEN [count]
			ELSE 0
		END
	) AS MedProvOn,
	SUM(
		CASE 
			WHEN ([role] = 'P1' OR [role] = 'MCP' OR [role] = 'HOS') AND status = 'OFF' THEN [count]
			ELSE 0
		END
	) AS MedProvOff
FROM
	@Table
GROUP BY
	nbr,
	[year]
ORDER BY 
	nbr,
	[year]