字段拆分

marvelous

要求从表1生成表2，网络上查找到很多从表2生成为表1，也有从表1生成表2的，但不是在ACCESS环境中。在表1中ID1为主关键字。

先谢谢了！

表1：
ID1           xm1
A           张三，李四
B           王五，王六，王七
C           王九

表2
ID2       xm2
A         张三
A         李四
B         王五
B         王六
B         王七
C         王九

marvelous

网络上有找到如下方法，但在ACCESS中如何解决？

拆分字段与合并字段是字符串处理常见的两个问题，下面将针对实例进行分析求解。
问题：將字符串拆分成記錄集
原表Table1(id int identity(1,1),name varchar(100))
id           name
-------------------
1            'a,b,c'
2            'e,f'

希望得到结果：
id    name
-----------
1     'a'
1     'b'
1     'c'
2     'e'
2     'f'

一、首先我们利用最原始的方法进行求解，即通过逐条循环来实现。
create table #a(id int identity(1,1),name varchar(100))
insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'
1.利用游标
DECLARE @id int,@name varchar(100)
create table #b(id int,name varchar(100))
DECLARE a_cursor CURSOR FOR
SELECT id, name
FROM #a
ORDER BY id
  
OPEN a_cursor
  
FETCH NEXT FROM a_cursor
INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN
  while charindex(',',@name)>0
    begin
insert #b(id,name)
select @id,left(@name,charindex(',',@name)-1)
set @name=right(@name,len(@name)-charindex(',',@name))
    end
  insert #b(id,name)
  select @id,@name
  FETCH NEXT FROM a_cursor
  INTO @id, @name
end
CLOSE a_cursor
DEALLOCATE a_cursor

select *from #b
drop table #b
2.利用临时表代替游标
DECLARE @id int,@name varchar(100),@count int
create table #tmp(no int identity(1,1),id int,name varchar(100))
create table #b(id int,name varchar(100))
insert #tmp(id,name)
select * from #a
set @count=@@rowcount
while @count>0
begin
  select @id=id,@name=name from #tmp where no=@count
  while charindex(',',@name)>0
    begin
insert #b(id,name)
select @id,left(@name,charindex(',',@name)-1)
set @name=right(@name,len(@name)-charindex(',',@name))
    end
  insert #b(id,name)
  select @id,@name
  set @count=@count-1
end

select * from #b order by id,name
drop table #b,#tmp
上述两种方法都两次利用了循环，清晰易懂，但代码长而且速度慢。

二、利用replace函数与动态语句结合。
-- test:
create table #a(id int identity(1,1),name varchar(100))
create table #b(id int,name varchar(100))
insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'
go
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' insert into #b select ' + cast(id as varchar) + ',''' + replace(name,',',''' union all select ' + cast(id as varchar) + ',''') + '''' from #a
--print @sql
exec (@sql)
go
select * from #b
go
drop table #a,#b

解析：此段代码很简洁，但使用了比较复杂的动态语句与代替函数的使用技巧，给程序的解读带来一定的困难，而且受字段串长度的限制，
一旦超过8000个字符就不能满足要求。

三、利用substring函数计算出每一段分割符的上下位置，从而达到分离字段的目的。
此方法可以处理较大数据量的要求而且速度较快，但此方法比较巧妙，难以联想到。
create table #a(id int identity(1,1),name varchar(100))

insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'

select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
from #a a join(
select top 100 id=a.id+b.id+1
from(
  select id=0 union all select 1
  union all select id=2 union all select 3
  union all select id=4 union all select 5
  union all select id=6 union all select 7
  union all select id=8 union all select 9
) a,(
  select id=0 union all select 10
  union all select id=20 union all select 30
  union all select id=40 union all select 50
  union all select id=60 union all select 70
  union all select id=80 union all select 90
) b
)b on substring(','+a.name,b.id,1)=','

drop table #a
综合起来，上述三种方法相比：
第一种通俗易懂，符合一般人的编写思想，但效率不高。
第二种方法语句短小精悍，较率也较高，但由于受字符串长度限制，通用性不好，难以处理大批量的数据。
第三种方法弥补了第二种方法的不足，但同时加进了程序的复杂度，不是很容易想得到，想得通。</