DECLARE	@Sample TABLe
	(
		ID INT,
		Col1 INT,
		Col2 INT,
		Col3 INT,
		Col4 INT
	);

INSERT	@Sample
VALUES	(1,  0, 0, 1, -1),
	(2,  1, 1, 1,  0),
	(3, -1, 0, 0,  1);

-- Solution by SwePeso
WITH cteSource(ID, theValue)
AS (
	SELECT	u.ID,
		u.theValue
	FROM	@Sample AS s
	UNPIVOT	(
			theValue
			FOR theColumn IN (s.Col1, s.Col2, s.Col3, s.Col4) -- This list should hold all columns involved in the calculation. Only one change in one place!
		) AS u
)
SELECT		ID,
		SUM(CASE WHEN theValue < 0 THEN 1 ELSE 0 END) AS [Neg Count],
		SUM(CASE WHEN theValue > 0 THEN 1 ELSE 0 END) AS [Pos Count],
		SUM(CASE WHEN theValue = 0 THEN 1 ELSE 0 END) AS [Zero Count]
FROM		cteSource
GROUP BY	ID
ORDER BY	ID;