1.测试数据---------------------------
create table CS (
id integer,
name varchar(100),
je numeric(18,2)
)
insert into CS (id, name,je )
select 1,'小红,小明',10
union all
select 2,'小红',10
union all
select 2,'小红,小黄,小绿',18
union all
select 2,'小红,小绿',18
union all
select 2,'小红,小绿,小明',17
2.转换-----------------------------
select a.id,a.je,
len(a.name) - len(replace(a.name, ',', '')) as dhs,--有多少个逗号
len(a.name) as zc,--字符串总长度
len(replace(a.name, ',', '')) as d,--替换逗号的字符串长度
charindex(',', a.name + ',', b.number) dhwz,--逗号位置
b.number,--起始位置
substring(a.name, b.number, charindex(',', a.name + ',', b.number) - b.number) as name
from CS a
join master..spt_values b on b.type = 'P'
where charindex(',', ',' + a.name, b.number) = b.number
3.也可以把某个需要均分的字段平均分--------------------
select id, je/rs as je, name
from (
select a.id,a.je,
len(a.name) - len(replace(a.name, ',', ''))+1 as rs,--人数
substring(a.name, b.number, charindex(',', a.name + ',', b.number) - b.number) as name
from CS a
join master..spt_values b on b.type = 'P'
where charindex(',', ',' + a.name, b.number) = b.number
) a