Matrix Query

Analytics
Enthusiast

Matrix Query

Hi,

I have a table like this .

Customer id Product Held Prod A Prod B Prod C Prod D Prod E
1001 A 1 1 0 1 1 0
1001 B 0 0 0 0 0 0
1001 C 1 1 0 1 1 0
1001 D 1 1 0 1 1 0
1001 E 0 0 0 0 0 0
1002 A 0 0 0 0 0 0
1002 B 1 0 1 0 1 1
1002 C 0 0 0 0 0 0
1002 D 1 0 1 0 1 1
1002 E 1 0 1 0 1 1
1003 A 1 1 1 0 0 0
1003 B 1 1 1 0 0 0
1003 C 0 0 0 0 0 0
1003 D 0 0 0 0 0 0
1003 E 0 0 0 0 0 0
1004 A 0 0 0 0 0 0
1004 B 1 0 1 1 0 1
1004 C 1 0 1 1 0 1
1004 D 0 0 0 0 0 0
1004 E 1 0 1 1 0 1
1005 A 0 0 0 0 0 0
1005 B 1 0 1 0 0 1
1005 C 0 0 0 0 0 0
1005 D 0 0 0 0 0 0
1005 E 1 0 1 0 0 1
1006 A 1 1 0 1 1 1
1006 B 0 0 0 0 0 0
1006 C 1 1 0 1 1 1
1006 D 1 1 0 1 1 1
1006 E 1 1 0 1 1 1
9 10 10 10 12

And i want to achieve something like this

Prod count(*) Prod A Prod B Prod C Prod D Prod E Total
---------------------------------------------------------------------
Prod A 3 3 1 2 2 1 9
Prod B 4 1 4 1 1 3 10
Prod C 3 2 1 3 2 2 10
Prod D 3 2 1 2 3 2 10
Prod E 4 1 3 2 2 4 12
17 9 10 10 10 12

Although i'm getting the counts right for every single product. Im not able to calculate the counts for customers having two or more products.
i.e customers having Product A as well as Product B

Have anyone done something like this?

Thanks in advance..

Although