(更正)如何达到em_g_no列每有三个元素自动换行:
Cop_g_no em_g_no
A 10,20,30
40,50,60
80,90
B 10,20,30
40,50,60
80,90,110
120
…. …
Cop_g_no em_g_no
A 10,20,30
40,50,60
80,90
B 10,20,30
40,50,60
80,90,110
120
…. …
create table A(Cop_g_no char(1),em_g_no varchar(100))
insert A
select 'A','10,20,30,40,50,60,80,90' union
select 'B','10,20,30,40,50,60,80,90,110,120'--创建函数
drop function ftest
go
create function ftest(@em_g_no varchar(100))
returns varchar(100)
as begin
declare @a int
declare @b int
declare @c int
declare @d varchar(100)
set @a=1
set @b=len(@em_g_no)
set @c=0
set @d=''
while @a<=@b begin
if substring(@em_g_no,@a,1)=',' begin
set @c=@c+1
if @c%3=0 begin
set @d=@d+substring(@em_g_no,len(@d)+1,@a-len(@d)-1)+char(13)
end
end
set @a=@a+1
end
set @d=@d+right(@em_g_no,len(@em_g_no)-len(@d))
return @d
end--调用
select Cop_g_no,dbo.ftest(em_g_no) em_g_no from A--结果
Cop_g_no em_g_no
-------- --------------
A 10,20,30
40,50,60
80,90
B 10,20,30
40,50,60
80,90,110
120(所影响的行数为 2 行)
請高手們指教啊
create proc ptest
as begin
set nocount on
declare @Cop_g_no char(1)
declare @em_g_no varchar(100)
declare @a int
declare @c int
declare @d varchar(100)
set @Cop_g_no=''
select * into #1 from A where 1=2
while @Cop_g_no is not null
begin
select @Cop_g_no = min(Cop_g_no) from A where Cop_g_no>@Cop_g_no
select @em_g_no=em_g_no from A where Cop_g_no=@Cop_g_no
if @Cop_g_no is not null begin
set @a=1
set @c=0
set @d=''
if len(@em_g_no)-len(replace(@em_g_no,',',''))<=2 or @em_g_no is null begin
insert #1 select @Cop_g_no,@em_g_no
end
else if len(@em_g_no)-len(replace(@em_g_no,',',''))=3 and right(@em_g_no,1)=',' begin
insert #1 select @Cop_g_no,left(@em_g_no,len(@em_g_no)-1)
end
else if len(@em_g_no)-len(replace(@em_g_no,',',''))>=3 and right(@em_g_no,1)<>',' begin
while @a<=len(@em_g_no) begin
if substring(@em_g_no,@a,1)=',' begin
set @c=@c+1
if @c%3=0 begin
if @c=3 begin
insert #1 select @Cop_g_no,substring(@em_g_no,len(@d)+1,+@a-len(+@d)-1)
end
else begin
insert #1 select '',substring(@em_g_no,len(@d)+1,@a-len(@d)-1)
end
set @d=@d+substring(@em_g_no,len(@d)+1,@a-len(@d))
end
end
set @a=@a+1
end
insert #1 select '',right(@em_g_no,len(@em_g_no)-len(@d))
end
end
end
set nocount off
select * from #1
end
--调用
exec ptest--结果
Cop_g_no em_g_no
-------- -----------
A 10,20,30
40,50,60
80,90
B 10,20,30
40,50,60
80,90,110
120