怎么样把两个字段里的内容，相对应的分多列显示啊？

Question

表 email 字段 A_ID , A_NAME(这两个字段需要分多列)，想把这些数据分开插入到新表 ATTA_NEW 中，如下：当前 email 表内容如下:E_ID   A_ID       A_NAME
--------------------------------------
1      11,32,21   AA.jpg*BB.jpg*CC.jpg
2      22         DD.doc
3      31,23      BB.xls*EE.gif
需要实现的结果,mail_new 表,AN_ID 是递增的,E_ID存放的是 email 的 E_ID ：
mail_new 表中 A_ID , A_NAME 的值分别是 email 表中A_ID ,A_NAME 的值需要的结果如下:
AN_ID    A_ID    A_NAME    E_ID
----------------------------------
1        11      AA.jpg    1
2        32      BB.jpg    1
3        21      CC.jpg    1
4        22      DD.doc    2
5        31      BB.xls    3
6        23      EE.gif    3
有大大能指导一下吗？

Answer 1

--> 测试数据：#Test
if object_id('tempdb.dbo.#Test') is not null drop table #Test
create table #Test(E_ID int, A_ID varchar(8), A_NAME varchar(20))
insert into #Test
select 1, '11,32,21', 'AA.jpg*BB.jpg*CC.jpg' union all
select 2, '22', 'DD.doc' union all
select 3, '31,23', 'BB.xls*EE.gif';with Pos1 (E_ID, Rid, P1, P2) as
(
select E_ID, 1, charindex(',', ','+A_ID), charindex(',', A_ID+',') + 1 from #Test
union all
select a.E_ID, b.Rid+1, b.P2, charindex(',', A_ID+',', b.P2) + 1 from #Test a join Pos1 b on a.E_ID = b.E_ID where charindex(',', A_ID+',', b.P2) > 0
),
Pos2 (E_ID, Rid, P1, P2) as
(
select E_ID, 1, charindex('*', '*'+A_NAME), charindex('*', A_NAME+'*') + 1 from #Test
union all
select a.E_ID, b.Rid+1, b.P2, charindex('*', A_NAME+'*', b.P2) + 1 from #Test a join Pos2 b on a.E_ID = b.E_ID where charindex('*', A_NAME+'*', b.P2) > 0
)
select AN_ID=row_number()over(order by a.E_ID, b.Rid), a.E_ID, A_ID = substring(a.A_ID+',', b.P1, b.P2-b.P1-1), A_NAME = substring(a.A_NAME+'*', c.P1, c.P2-c.P1-1) from #Test a join Pos1 b on a.E_ID = b.E_ID join Pos2 c on b.E_ID = c.E_ID and b.Rid = c.Rid option (maxrecursion 0)/*
AN_ID       E_ID        A_ID      A_NAME
----------- ----------- --------- ---------------------
1           1           11        AA.jpg
2           1           32        BB.jpg
3           1           21        CC.jpg
4           2           22        DD.doc
5           3           31        BB.xls
6           3           23        EE.gif
*/
XML方法哪个你自己想吧，XML处理效率很低。

Answer 2

一列转多行。
SQL code
/*
标题：分拆列值
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述有表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 TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #--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)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 3

if OBJECT_ID('emails') is not null
 drop table emails;
go
create table emails (e_id int,a_id varchar(20),a_name varchar(100));
go
insert into emails values(1,'11,32,21','aa.jpg*bb.jpg*cc.jpg');
insert into emails values(2,'22','dd.doc');
insert into emails values(3,'31,23','bb.xls*ee.gif');
go;with t1 as(
select e_id,
cast('<id>'+REPLACE(a_id,',','</id><id>')+'</id>' as xml) a_id,
cast('<name>'+REPLACE(a_name,'*','</name><name>')+'</name>' as xml) a_name
from emails
),
t2 as(
select e_id,a_id,a_name,v.number
from t1,master.dbo.spt_values v
where v.type='P' and v.number between 1 and a_id.value('fn:count(/id)','int')
)
select row_number() over (order by e_id) an_id,
cast(a_id.query('data(/id[sql:column("number")])') as varchar(10)) a_id,
cast(a_name.query('data(/name[sql:column("number")])') as varchar(30)) a_name,
e_id
from t2;
/*
1 11 aa.jpg 1
2 32 bb.jpg 1
3 21 cc.jpg 1
4 22 dd.doc 2
5 31 bb.xls 3
6 23 ee.gif 3
*/