sqlserver列分割转多行一列转多行

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