0

I have customer data in a table structure like this:

customerID | status       | 
xxx        | 1            |
xxx        | 2            | 
yyy        | 3            |
xxx        | 1            |
yyy        | 4            |
yyy        | 4            |

I would now like to see the different status each customer have

So the result would be:

customerID | status1     |  status2      |  status3      |  status4
xxx        |  2          | 1             |  0            | 0
yyy        |  0          | 0             |  1            | 2
  • How many different status do you have? – McNets Mar 13 '18 at 14:54
  • @McNets In that case lets say i have only 4. – Nick Mar 13 '18 at 14:56
2
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);

If there are a well-known different status values, you can use a PIVOT solution in this way:

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

Result:

customerID |  1 |  2 |  3 |  4
:--------- | -: | -: | -: | -:
XXX        |  2 |  1 |  1 |  1
YYY        |  0 |  2 |  0 |  1

dbfiddle here

Or you can use SUM(CASE WHEN status = ...)

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;

Result:

customerID | Status1 | Status2 | Status3 | Status4
:--------- | ------: | ------: | ------: | ------:
XXX        |       2 |       1 |       1 |       1
YYY        |       0 |       2 |       0 |       1

dbfiddle here

| improve this answer | |
  • Just out of curiosity. Is there another way of doing this without PIVOT? – Nick Mar 13 '18 at 15:09
  • Added to the answer – McNets Mar 13 '18 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.