如何把一行里的数据------------变成多行？

Question

就是要把location这个字段里的值，分折到每一行。。
如何在ACCESS里处理？
比如，
AutoID      PartID          Location
-----   --------------  --------------
14 HAMP-025-0002 C190 C234 C237
15 HAMP-025-0038 D27 D28 D29
...变成PartID          Location
--------------  --------------
HAMP-025-0002 C190 
HAMP-025-0002 C234 
HAMP-025-0002 C237
HAMP-025-0038 D27 
HAMP-025-0038 D28
HAMP-025-0038 D29
.....

Answer 1

--AutoID PartID Locationselect a.AutoID,a.PartID,
    Location=substring(a.Location,b.number,charindex(' ',a.Location+' ',b.number)-b.number)
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.Location)
    and substring(' '+a.Location,b.number,1) = ' '

Answer 2

MSSQL可以处理
你搜下分隔字符串
但是access不知道怎么弄

Answer 3

SELECT PartID,mid(a2,instr(a2," ")+1) as L from(
SELECT PartID, left(Location,InStr(Location," ")) AS a1,mid(Location,InStr(Location," ")+1) as a2 FROM tb
) AS t
union all
SELECT PartID, Left(a2,InStr(a2," ")) AS L from(
SELECT PartID, left(Location,InStr(Location," ")) AS a1,mid(Location,InStr(Location," ")+1) as a2 FROM tb
) AS t
union all
SELECT PartID, left(Location,InStr(Location," ")) AS L FROM tb order by PartID

Answer 4

ACCESS不会，关注，高手把代码贴出来，学习借鉴一下

Answer 5

ACCESS的建议去其他数据库版问问

Answer 6

所有数据库版块就MSSQL最火了吧,在本论坛里.

Answer 7

declare @t table(autoid int,partid varchar(50),location varchar(100));
insert into @t(autoid,partid,location)
select 14,'HAMP-025-0002','C190 C234 C237' union all
select 15,'HAMP-025-0038','D27 D28 D29';
--select * from @t;
select a.autoId,a.partId,b.vx
from 
(select autoid,partid,cast('<root><v>'+REPLACE(location,' ','</v><v>')+'</v></root>' as xml) as x from @t) a
outer apply(
select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v)
) b/*
autoId      partId                                             vx
----------- -------------------------------------------------- -----------
14          HAMP-025-0002                                      C190
14          HAMP-025-0002                                      C234
14          HAMP-025-0002                                      C237
15          HAMP-025-0038                                      D27
15          HAMP-025-0038                                      D28
15          HAMP-025-0038                                      D29*/

Answer 8

这样，先导出到MSSQL，处理完后再导入ACCESS～～

Answer 9

测试varchar可以通过，但因为Location是ntext格式，所以提示：“对数据类型而言运算符无效。运算符为 add，类型为 ntext。”也就是location太长了，数据类型也不对，怎么办？

Answer 10

把 location 换成 cast(location as nvarchar(max))max  不行就 4000  试试！