看看大乌龟的代码/* 标题:简单数据拆分(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) create table tb(id int,item varchar(100)) insert into tb values(1 ,'15-107-110;15-108-006;15-108-101;15-113-002;15-119-001;15-119-001;15-158-004;15-201-206;') insert into tb values(2 ,'15-107-110;15-108-006;15-108-101;15-113-002;15-119-001;15-119-001;15-158-004;15-201-206;') goselect a.id , item = substring(a.item , b.number , charindex(';' , a.item + ';' , b.number) - b.number) from tb a join master..spt_values b on b.type='p' and b.number between 1 and len(a.item) where substring(';' + a.item , b.number , 1) = ';'drop table tb/* id item ----------- ---------------------------------------------------------------------------------------------------- 1 15-107-110 1 15-108-006 1 15-108-101 1 15-113-002 1 15-119-001 1 15-119-001 1 15-158-004 1 15-201-206 2 15-107-110 2 15-108-006 2 15-108-101 2 15-113-002 2 15-119-001 2 15-119-001 2 15-158-004 2 15-201-206(所影响的行数为 16 行) */
--> 测试数据: #tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (ID int,CityName varchar(14) ) insert into #tb select 1,'深圳,东莞' union all select 2,'北京,石家庄' SELECT id,b.v FROM (SELECT id,CAST('<r>' + REPLACE(CityName,',','</r><r>') + '</r>' AS XML) x from #tb) a CROSS APPLY (SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b id v ----------- ---------- 1 深圳 1 东莞 2 北京 2 石家庄(4 行受影响)
标题:简单数据拆分(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)
create table tb(id int,item varchar(100))
insert into tb values(1 ,'15-107-110;15-108-006;15-108-101;15-113-002;15-119-001;15-119-001;15-158-004;15-201-206;')
insert into tb values(2 ,'15-107-110;15-108-006;15-108-101;15-113-002;15-119-001;15-119-001;15-158-004;15-201-206;')
goselect a.id , item = substring(a.item , b.number , charindex(';' , a.item + ';' , b.number) - b.number)
from tb a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.item)
where substring(';' + a.item , b.number , 1) = ';'drop table tb/*
id item
----------- ----------------------------------------------------------------------------------------------------
1 15-107-110
1 15-108-006
1 15-108-101
1 15-113-002
1 15-119-001
1 15-119-001
1 15-158-004
1 15-201-206
2 15-107-110
2 15-108-006
2 15-108-101
2 15-113-002
2 15-119-001
2 15-119-001
2 15-158-004
2 15-201-206(所影响的行数为 16 行)
*/
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (ID int,CityName varchar(14) )
insert into #tb
select 1,'深圳,东莞' union all
select 2,'北京,石家庄' SELECT id,b.v FROM
(SELECT id,CAST('<r>' + REPLACE(CityName,',','</r><r>') + '</r>' AS XML) x from #tb) a
CROSS APPLY
(SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b
id v
----------- ----------
1 深圳
1 东莞
2 北京
2 石家庄(4 行受影响)