CREATE TABLE tbl (customerID varchar(10), status int); INSERT INTO tbl VALUES ('XXX', 1), ('XXX', 2), ('XXX', 3), ('YYY', 2), ('YYY', 2), ('XXX', 1), ('XXX', 4), ('YYY', 4);

SELECT customerID, [1], [2], [3], [4] FROM ( SELECT customerID, status FROM tbl ) src PIVOT ( COUNT(status) FOR status IN ([1], [2], [3], [4]) ) pvt;

SELECT customerID, SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) Status1, SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) Status2, SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) Status3, SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END) Status4 FROM tbl GROUP BY customerID;