Script to split a Multi-valued attribute

drop table simplesales2;

/**/select * into simplesales2 from simplesales1;

alter table simpleSales2  add colors varchar(50), color1 varchar(25), color2 varchar(25), color3 varchar(25);


update simplesales2 set colors=color;

update simplesales2 set color1=left(colors, charindex(',',colors+',')-1);

update simplesales2 set colors=right(color, len(colors) - charindex(',',colors+','));


update simplesales2 set color2=left(colors, charindex(',',colors+',')-1);


update simplesales2 set colors = right(colors+ ' ', len(colors)+1 - charindex(',',colors+',')) FROM simplesales2

update simplesales2 set color3=left(colors, charindex(',',colors+',')-1);

alter table simplesales2 drop column colors, color;


SELECT * FROM simplesales2