急救!!存储过程:入参 数组如何分离并循环插入表中？

Question

分拆列值 有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 1. 旧的解决方法(sql server 2000) 
 
select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number) 
FROM tb a, master..spt_values  b 
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ',' 
2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT a.id, b.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)a 
OUTER aPPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v) 
)b DROP TabLE tb /* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc (5 行受影响) 
*/

Answer 1

分拆列值 有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 1. 旧的解决方法(sql server 2000) 
 
select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number) 
FROM tb a, master..spt_values  b 
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ',' 
2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT a.id, b.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)a 
OUTER aPPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v) 
)b DROP TabLE tb /* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc (5 行受影响) 
*/
...

Answer 2

SQL2005的话，看看CLR Stored Proc
另外，直接采用字符串解析也不难呀

Answer 3

select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number) 
FROM tb a, master..spt_values  b 
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ',' 照这个例子改就是了!

Answer 4

CREATE function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))   
  returns @temp table(a varchar(100))   
  /*--实现split功能的函数*/   
  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 DECLARE @Str NVARCHAR(1000)
SET @Str='a:a1:a2,b:b1:b2,c:c1:c2'
SELECT id=IDENTITY(int, 1,1),* into #tb FROM dbo.f_split(@Str,',')
SELECT id,B.a from #tb A CROSS APPLY dbo.f_split(A.a,':') B
DROP table #tb
id          a
----------- ------
1           a
1           a1
1           a2
2           b
2           b1
2           b2
3           c
3           c1
3           c2(9 行受影响)

Answer 5

declare @str varchar(50), @split char(1)declare @integers table(i int identity, n bit)
insert @integers(n)
select top 50 0 from sysobjects
set @str = 'a:a1:a2，b:b1:b2，c:c1:c2'
set @split = ','select identity(int) as F, ltrim(rtrim(substring(@str, i, charindex(@split, @str + @split, i) - i))) as D
into #1
from @integers
where i <= len(@str + @split) and charindex(@split, @split + @str, i) = i
order by iset @split = ':'select F, ltrim(rtrim(substring(D, i, charindex(@split, D + @split, i) - i))) as D
from #1 join @integers
on i <= len(D + @split) and charindex(@split, @split + D, i) = i
order by F, idrop table #1
/*
F           D                                                  
----------- -------------------------------------------------- 
1           a
1           a1
1           a2
2           b
2           b1
2           b2
3           c
3           c1
3           c2
*/