--创建数据测试环境
declare @tb table(degree varchar(8000))
insert into @tb
select '34,55,66,77,88'
union all select '55,66,77,88,99'
union all select '66,55,88,44.88'
--创建数据处理临时表
select top 8000 id=identity(int,1,1) into #tb
from(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) b,
(select top 100 id from syscolumns) c
declare @sql varchar(8000),@i int
--准备数据处理的临时表
select id=identity(int,1,1),degree into #tb1 from @tb
select id=identity(int,1,1),cast(a.id as int) as oldid
,cast(substring(degree,b.id,charindex(',',degree+',',b.id)-b.id) as decimal(38,8))as newname
into #temp from #tb1 a,#tb b
where substring(','+degree,b.id,1)=','
--生成数据处理的SQL语句
select @sql='',@i=aa
from(select top 1 oldid,count(id) as aa
from #temp group by oldid order by count(id) desc) a
while @i>0
select @sql=char(13)+',max(case a.id-b.id when '+cast(@i-1 as varchar)
+' then a.newname end) as ['+cast(@i as varchar)
+']'+@sql
,@i=@i-1
set @sql='select a.oldid'+char(13)+@sql
+char(13)+' from #temp a'
+char(13)+' ,(select oldid,min(id) as id from #temp group by oldid) b'
+char(13)+'where a.oldid=b.oldid'
+char(13)+'group by a.oldid'
--创建数据测试环境
declare @tb table(degree varchar(8000))
insert into @tb
select '34,55,66,77,88'
union all select '55,66,77,88,99'
union all select '66,55,88,44.88'
--创建数据处理临时表
select top 8000 id=identity(int,1,1) into #tb
from(select top 100 id from syscolumns) a,
(select top 100 id from syscolumns) b,
(select top 100 id from syscolumns) c
--处理数据
select * from(
select degree=cast(substring(degree,b.id,charindex(',',degree+',',b.id)-b.id) as decimal(38,8))
from @tb a,#tb b
where substring(','+degree,b.id,1)=','
) a order by degree
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
-执行
select * from dbo.f_splitstr('1,2,3,4',',')