<!-- --> > 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); > GO > > <pre> 8 rows affected > </pre> <!-- --> > SELECT customerID, [1], [2], [3], [4] > FROM > ( > SELECT customerID, status > FROM tbl > ) src > PIVOT > ( > COUNT(status) FOR status IN ([1], [2], [3], [4]) > ) pvt; > GO > > <pre> > customerID | 1 | 2 | 3 | 4 > :--------- | -: | -: | -: | -: > XXX | 2 | 1 | 1 | 1 > YYY | 0 | 2 | 0 | 1 > </pre> <!-- --> > 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; > GO > > <pre> > customerID | Status1 | Status2 | Status3 | Status4 > :--------- | ------: | ------: | ------: | ------: > XXX | 2 | 1 | 1 | 1 > YYY | 0 | 2 | 0 | 1 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c2faf0d1fa4d41059bbdd83f347a3e6d)*