I have the input ID Class 0 A 0 A 0 B 1 A 1 C 1 C 2 B 2 B 2 C What I want is to extract distinct class values in each ID group by, the output will be something like this: ID Class A Class B Class C 0 2 1 0 1 1 0 2 2 0 2 1 My thought is to use count distinct inside group by something like: select ID,
sum(if(Class='A', 1, 0) as Class_A,
sum(if(Class='B', 1,0) as Class_B,
sum(if(Class='C', 1, 0) as Class_C
from table
group by ID But then the problem is that it's inefficiency if we have 100 classes. Any idea we can auto-extract count of distinct classes in group by?
... View more