现有数据表
id name sort
1 张三 A-B-C-D-E
2 李四 S-D-K-P-B
3 王五 Y-H-J-V-W
想把sort字段按“-”分割，然后保存到另外一个表，像下面这样
id name s1 s2 s3 s4 s5
1 张三 A B C D E
2 李四 S D K P B
3 王五 Y H J V W
MSSQL貌似没有类似于split这样的函数。如果能有一条语句就达到效果最好了，实在不行存储过程也可以
------解决思路----------------------
这算不算取巧?
;with tbl(id,name,sort) as
(
select 1, '张三', 'A-B-C-D-E' union all
select 2, '李四', 'S-D-K-P-B' union all
select 3, '王五', 'Y-H-J-V-W'
),
t1 as
(
select id, (substring(sort,CHARINDEX('-', sort)+1, LEN(sort))) as sort from tbl
),
t2 as
(
select id, (substring(sort,CHARINDEX('-', sort)+1, LEN(sort))) as sort from t1
),
t3 as
(
select id, (substring(sort,CHARINDEX('-', sort)+1, LEN(sort))) as sort from t2
),
t4 as
(
select id, (substring(sort,CHARINDEX('-', sort)+1, LEN(sort))) as sort from t3
)
select
t.id, t.name, t.sort,
SUBSTRING(t.sort, 0, CHARINDEX('-', t.sort)) as s1,
SUBSTRING(t1.sort, 0, CHARINDEX('-', t1.sort)) as s2,
SUBSTRING(t2.sort, 0, CHARINDEX('-', t2.sort)) as s3,
SUBSTRING(t3.sort, 0, CHARINDEX('-', t3.sort)) as s4,
t4.sort as s5
from tbl as t
left join t1
on t.id = t1.id
left join t2
on t.id = t2.id
left join t3
on t.id = t3.id
left join t4
on t.id = t4.id
------解决思路----------------------
with tb(id,name,sort) as
(
select 1,'张三','A-B-C-D-E' union all
select 2,'李四','S-D-K-P-B' union all
select 3,'王五','Y-H-J-V-W'
)
select * from
(
select a.id,a.name,SUBSTRING(a.sort,b.number,CHARINDEX('-',a.sort+'-',b.number)-b.number)sort,'s'+convert(nvarchar(10),ROW_NUMBER()over(partition by a.id,a.name order by id))col
from tb a
inner join master..spt_values b on LEN(a.sort) between 0 and 2048
where SUBSTRING('-'+a.sort,b.number,1)='-' and b.type='P'
) p pivot (max(sort) for col in(s1,s2,s3,s4,s5))t
order by id