I am trying to get a count from a table which has 4 columns. The 2 I have included in the pivot_data are 'ocode' and 'cname' the other 2 relate to customer details. I am trying to show how many individual 'ocode' (options) there are for each 'cname' (product) so both the product and option can appear any number of times in any number of combinations.

I currently have the following:

```
WITH pivot_data AS(
SELECT ocode,cname
FROM mi17
)
SELECT *
FROM pivot_data
PIVOT(
COUNT(ocode)
FOR cname
IN ('Dog','Cat','Horse')
);
```

This results in the following

```
Dog Cat Horse
1 2 3
```

However the ocode values will be different e.g. 'OC1', 'OC2', 'OC3' . what I would like is:

```
Dog Cat Horse
OC1 1 1 1
OC2 0 1 0
OC3 0 0 2
```