remove multi value attribute

drop table simplesales1 ;

/**/ select *  into simplesales1 from stud402.simplesales;

alter table simplesales1 add colors varchar(100), color1 varchar(25), color2 varchar(25), color3 varchar(25);


update simplesales1 set  colors=color;

update simplesales1 set color1= left(  colors, charindex(',',colors)-1)  ;
update simplesales1 set colors =  substring(colors, charindex(',',colors)+1, 1000) ;

update simplesales1 set color2= left(  colors, charindex(',',colors+',')-1)  ;
update simplesales1 set colors =  substring(colors, charindex(',',colors+',')+1, 1000) ;

update simplesales1 set color3= left(  colors, charindex(',',colors+',')-1)  ;
update simplesales1 set colors =  substring(colors, charindex(',',colors+',')+1, 1000) ;

ALTER TABle simplesales1 drop column colors, color;



select *  from simplesales1