use DB01;
-- 建表
create table dbo.tb_hobby(
id int null
,name varchar(10) null
,hobby varchar(100) null
);
-- 插入数据
insert into dbo.tb_hobby values ('1001','朱梅拉','跑步,踢足球,打篮球');
insert into dbo.tb_hobby values ('1002','李格策','书法,跑步');
-- 查询
select * from dbo.tb_hobby;
-- 拆分成行
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)=','
