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