如果把一个字段拆分成多条记录？

例如，有如下T表：
Id   f1   f2
1    a,b  i,j
2    c,d  x,y

拆分成：
Id   f1   f2
1    a    i
1    b    j
2    c    x
2    d    y

SQL怎么写？谢谢。

11 个解决方案

#1

/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：

有表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)

create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go

--方法1.使用临时表完成
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','

DROP TABLE #

--方法2.如果数据量小，可不使用临时表
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) 
from tb a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.value)
where 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

--方法1.使用xml完成
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

--方法2.使用CTE完成
;with tt as 
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)


DROP TABLE tb

/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
*/

#2

select Id,left(f1,1) as f1,left(f2,1) as f2 from tb
union all
select Id,right(f1,1),right(f2,1) from tb
order by id

#3

如果只有一个逗号,则参考如下,如果是多个逗号,则参考一楼.

create table tb(Id int ,f1 varchar(10),f2 varchar(10)) 
insert into tb values(1 , 'a,b' , 'i,j') 
insert into tb values(2 , 'c,d' , 'x,y')
go

select id , f1=left(f1 , charindex(',',f1) - 1) , f2=left(f2 , charindex(',',f2) - 1) from tb
union all
select id  ,f1=substring(f1,charindex(',',f1) + 1,len(f1)),f2=substring(f2,charindex(',',f2) + 1,len(f2)) from tb
order by id

drop table tb 

/*
id          f1         f2         
----------- ---------- ---------- 
1           a          i
1           b          j
2           d          y
2           c          x

（所影响的行数为 4 行）
*/

#4

引用 1 楼 dawugui 的回复:

SQL code
/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：

有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆valu……

请问：第三个字段怎么处理？
Id       f1       f2
1         a,b     i,j
2         c,d     x,y 

#5

引用 3 楼 dawugui 的回复:

如果只有一个逗号,则参考如下,如果是多个逗号,则参考一楼.
SQL code
create table tb(Id int ,f1 varchar(10),f2 varchar(10)) 
insert into tb values(1 , 'a,b' , 'i,j') 
insert into tb values(2 , 'c,d' , 'x,y')
go

select id , f1=……

有多个逗号，且至少有2个字段需要拆分组合。

#6

Id f1 f2
1 a,b i,j,n,m

如果是这样的，你希望拆出来的行数怎么排？

#7

引用 6 楼 ssp2009 的回复:

Id f1 f2
1 a,b i,j,n,m

如果是这样的，你希望拆出来的行数怎么排？

同问.

#8

引用 6 楼 ssp2009 的回复:

Id f1 f2
1 a,b i,j,n,m

如果是这样的，你希望拆出来的行数怎么排？

f1和f2这两个字段是逗号一样多，也就是按顺序一一对应的。

#9

引用 8 楼 blackeagle_ 的回复:

引用 6 楼 ssp2009 的回复:

Id f1 f2
1 a,b i,j,n,m

如果是这样的，你希望拆出来的行数怎么排？


f1和f2这两个字段是逗号一样多，也就是按顺序一一对应的。

如果是这样的话,sql 2000需要使用临时表,sql 2005可用row_number()(其方法在一楼已经有介绍)

create table tb(Id int ,f1 varchar(10),f2 varchar(10)) 
insert into tb values(1 , 'a,b' , 'i,j') 
insert into tb values(2 , 'c,d' , 'x,y')
go

select a.id , f1 = substring(a.f1 , b.number , charindex(',' , a.f1 + ',' , b.number) - b.number) ,
px = identity(int,1,1) into tmp1
from tb a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.f1)
where substring(',' + a.f1 , b.number , 1) = ','

select a.id , f2 = substring(a.f2 , b.number , charindex(',' , a.f2 + ',' , b.number) - b.number) ,
px = identity(int,1,1) into tmp2
from tb a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.f2)
where substring(',' + a.f2 , b.number , 1) = ','

select m.id , m.f1 , n.f2 from tmp1 m, tmp2 n where m.px = n.px

drop table tb , tmp1,tmp2

/*
id          f1         f2         
----------- ---------- ---------- 
1           a          i
1           b          j
2           c          x
2           d          y

（所影响的行数为 4 行）

*/

#10

create table tb(Id int ,f1 varchar(10),f2 varchar(10)) 
insert into tb values(1 , 'a,b' , 'i,j') 
insert into tb values(2 , 'c,d' , 'x,y')
go
select m.id , m.f1 , n.f2 from 
(
SELECT A.id, B.f1,px = row_number() over(order by a.id) FROM
(
  SELECT id, f1 = CONVERT(xml,'<root><v>' + REPLACE(f1, ',', '</v><v>') + '</v></root>') FROM tb
) A OUTER APPLY
(
  SELECT f1 = N.v.value('.', 'varchar(100)') FROM A.[f1].nodes('/root/v') N(v)
) B
) m,
(
SELECT A.id, B.f2,px = row_number() over(order by a.id) FROM
(
  SELECT id, f2 = CONVERT(xml,'<root><v>' + REPLACE(f2, ',', '</v><v>') + '</v></root>') FROM tb
) A OUTER APPLY
(
  SELECT f2 = N.v.value('.', 'varchar(100)') FROM A.[f2].nodes('/root/v') N(v)
) B
) n
where m.px = n.px

drop table tb

/*
id          f1                                                                                                   f2
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           a                                                                                                    i
1           b                                                                                                    j
2           c                                                                                                    x
2           d                                                                                                    y

(4 行受影响)

*/

#11

Thank All!