available data , Like hobbies hobby, There are multiple hobbies separated by commas and stored together , Now we need to separate them

final result

Direct script
use DB01; -- Build table create table dbo.tb_hobby( id int null ,name varchar(10) null
,hobby varchar(100) null ); -- insert data insert into dbo.tb_hobby values
('1001',' Jumeira ',' run , play football , Play basketball '); insert into dbo.tb_hobby values
('1002',' Li Gece ',' Calligraphy , run '); -- query select * from dbo.tb_hobby; -- Split into rows select a.id
,a.name ,SUBSTRING(a.hobby,number,CHARINDEX(',',a.hobby+',',number)-number) as
hobby from tb_hobby a,master..spt_values where number >= 1 and number <
len(a.hobby) and type='p' and SUBSTRING(','+a.hobby,number,1)=',' ;