select substring(姓名,b.number,charindex(',',姓名+',',b.number)-b.number) 姓名,成绩 from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(姓名) and substring(','+姓名,b.number,1) = ','--然后去做统计
if object_id('[TB]') is not null drop table [TB] go create table [TB] (姓名 nvarchar(10),成绩 INT) insert into [TB] select '甲,乙',80select * from [TB]select b.姓名,a.成绩 from (select 成绩,姓名=convert(xml,'<root><v>'+replace(姓名,',','</v><v>')+'</v></root>') from [TB])a outer apply (select 姓名=C.v.value('.','nvarchar(100)') from a.姓名.nodes('/root/v')C(v))b
select substring(姓名,b.number,charindex(',',姓名+',',b.number)-b.number) 姓名,成绩
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(姓名)
and substring(','+姓名,b.number,1) = ','--然后去做统计
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (姓名 nvarchar(10),成绩 INT)
insert into [TB]
select '甲,乙',80select * from [TB]select
b.姓名,a.成绩
from
(select 成绩,姓名=convert(xml,'<root><v>'+replace(姓名,',','</v><v>')+'</v></root>') from [TB])a
outer apply
(select 姓名=C.v.value('.','nvarchar(100)') from a.姓名.nodes('/root/v')C(v))b
/*
成绩 姓名
80 甲
80 乙*/