Grouping a comma separated value on common data [closed]
- by Ankit
I have a table with col1 id int, col2 as varchar (comma separated values) and column 3 for assigning group to them.
Table looks like
col1 col2 group
..............................
1 2,3,4
2 5,6
3 1,2,5
4 7,8
5 11,3
6 22,8
This is only the sample of real data, now I have to assign a group no to them in such a way that output looks like
col1 col2 group
..............................
1 2,3,4 1
2 5,6 1
3 1,2,5 1
4 7,8 2
5 11,3 1
6 22,8 2
The logic for assigning group no is that every similar comma separated value of string in col2 have to be same group no as every where in col2 where '2' is there it has to be same group no but the complication is that 2,3,4 are together so they all three int value if found in any where in col2 will be assigned same group.
The major part is 2,3,4 and 1,2,5 both in col2 have 2 so all int 1,2,3,4,5 have to assign same group no.
Tried store procedure with match against on col2 but not getting desired result
Most imp (I can't use normalization, because I can't afford to make new table from my original table which have millions of records), even normalization is not helpful in my context.
This question is also on stackoverflow with bounty on this link
Achieved so far:-
I have set the group column auto increment and then wrote this procedure:-
BEGIN
declare cil1_new,col2_new,group_new int;
declare done tinyint default 0;
declare group_new varchar(100);
declare cur1 cursor for select col1,col2,`group` from company ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open cur1;
REPEAT
fetch cur1 into col1_new,col2_new,group_new;
update company set group=group_new where
match(col2) against(concat("'",col2_new,"'"));
until done end repeat;
close cur1;
select * from company;
END
This procedure is working, no syntax mistake but the problem is that I am not achieving the desired result exactly.
