SQL 如何将字符串'a1,b1,c1;a2,b2;a3,b3,c3,d3' 转换成表

Question

SQL 如何将字符串'a1,b1,c1;a2,b2;a3,b3,c3,d3' 转换成表 ','分列';'分行  sField1 sField2 sField3 sField4
 a1        b1      c1 
 a2        b2
 a3        b3      c3      d3
写成一个能用的函数

Answer 1

create function [dbo].[function_fenggezifu](@a nvarchar(4000),@b varchar(50))
returns @t table(h varchar(4000))
as
begin
declare @i int,@c varchar(4000)
set @i = charindex(@b,@a)
while @i>=1
begin
    set @c=LTRIM(RTRIM(@c)) 
    set @c=substring(@a,1,@i-1)
    set @a=substring(@a,@i+1,len(@a)-@i)
    set @i=CHARINDEX(@b,@a)
    set @c=LTRIM(RTRIM(@c))
    if @c!='' and @c is not null 
    begin
        insert into @t values(@c)
    end
end
if @a!='' and @a is not null
begin
    insert into @t values(@a)
end
return
end
create table aaa(bbb varchar(80))
insert into aaa 
select 'a1,b1,c1;a2,b2;a3,b3,c3,d3'union all
select 'a1,b1,c1;a2,b2;a3,b3,c3,d3,e3,f3;a4,b4,c4,d4,c4' union all
select 'a1,b1,c1;a2,b2;a3,b3,c3,d3;a4,b4,c4,d4,c4'create table tb(sField1 varchar(50))
declare @column_cou int,@column int,@sql varchar(1000)
declare @i int,@count int,@shumu int,@name varchar(100)
create table #tb (row int,h varchar(100))
insert into #tb
        select ROW_NUMBER()over(order by c.h)as row,replace('select '''+c.h+'''',',',''',''') 
        from (
            select h 
            from aaa as a cross apply [function_fenggezifu](a.bbb,';')) as c
set @column_cou=(
    select top 1 count(e.h) 
    from    #tb as d cross apply [function_fenggezifu](d.h,',') as e group by d.row order by count(e.h) desc
)select @column=max(cast(SUBSTRING(name,7,LEN(name)) as int)) 
from sys.all_columns 
where object_id=(
    select object_id 
    from sys.tables 
    where name='tb')
if @column<@column_cou
    begin
        while @column<=@column_cou-1
        begin 
            set @column=@column+1
            exec('alter table tb add sField'+@column+' varchar(50)')
        end
    end
select @count=MAX(row) from #tb
set @i=1
while @i<=@count
    begin
        select @shumu=COUNT(*) from #tb as a cross apply [function_fenggezifu](a.h,',') where a.row=@i
        set @name=''
        select @name=@name+name+','
        from sys.all_columns 
        where object_id=(
            select object_id 
            from sys.tables 
            where name='tb') and cast(SUBSTRING(name,7,LEN(name)) as int)<=@shumu
        set @name=SUBSTRING(@name,0,LEN(@name))
        select @sql=h from #tb where row=@i        set @sql='insert into tb('+@name+')'+@sql
        exec(@sql)
        set @i=@i+1
    end
drop table #tb
select * from tb
-----------------------------------
sField1                                            sField2                                            sField3                                            sField4                                            sField5                                            sField6
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
a1                                                 b1                                                 c1                                                 NULL                                               NULL                                               NULL
a1                                                 b1                                                 c1                                                 NULL                                               NULL                                               NULL
a1                                                 b1                                                 c1                                                 NULL                                               NULL                                               NULL
a2                                                 b2                                                 NULL                                               NULL                                               NULL                                               NULL
a2                                                 b2                                                 NULL                                               NULL                                               NULL                                               NULL
a2                                                 b2                                                 NULL                                               NULL                                               NULL                                               NULL
a3                                                 b3                                                 c3                                                 d3                                                 NULL                                               NULL
a3                                                 b3                                                 c3                                                 d3                                                 NULL                                               NULL
a3                                                 b3                                                 c3                                                 d3                                                 e3                                                 f3
a4                                                 b4                                                 c4                                                 d4                                                 c4                                                 NULL
a4                                                 b4                                                 c4                                                 d4                                                 c4                                                 NULL(11 行受影响)