Find the count of the elements in the column

[Ramu_i]

Hi Team,

Data 

ID	Column
1	A,B,C,D
2	A,D,E
1	A,D,F,G
1	G,A,B,D,N
2	A, G,D
2	D
2	K
3	H
4	H,K
5	A,B,D
5	D,B,C
5	A,D

Required Output is

ID	Column_Values	Count
1	A,B,C,D,F,G,N	7
2	A,D,E,G,K	5
3	H	1
4	H,K	2
5	A,B,D,C	4

How to find the requirement.

Thanks advance.

Ram

[marcus_sommer · Accepted solution]

Maybe with:

t1: load ID, subfield(Column, ',') as C1 from X;

t2: load ID, concat(distinct C1, ',') as C2 resident t1 group by ID;

t3: load ID, C2, substringcount(C2, ',') + 1 as C3 resident t2;

drop tables t1, t2;