create table tb(a varchar(10),b varchar(20)) insert into tb select '123','8,9,20' insert into tb select 'as','23,24,25' insert into tb select 'vv','16,17,18' go select a.a,substring(a.b,b.number,charindex(',',a.b+',',b.number+1)-b.number)b from tb a,master..spt_values b where b.type='p' and b.number<=len(a.b) and substring(a.b,b.number,1)<>',' and substring(','+a.b,b.number,1)=',' /* a b ---------- -------------------- 123 8 123 9 123 20 as 23 as 24 as 25 vv 16 vv 17 vv 18(9 行受影响) */ go drop table tb
create table #tb(a varchar(10),b varchar(20)) insert into #tb select '123','8,9,20' union all select 'as','23,24,25' union all select 'vv','16,17,18' select m.a,b=SUBSTRING(m.b,n.number,charindex(',',m.b+',',n.number)-n.number) from #tb m,master..spt_values n where n.type='p' and n.number<=LEN(m.b) and CHARINDEX(',',','+m.b,n.number)=n.number a b ---------- -------------------- 123 8 123 9 123 20 as 23 as 24 as 25 vv 16 vv 17 vv 18
/* 标题:分拆列值1 作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 时间: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, 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. 新的解决方法(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 行受影响) */
在系统数据库中有个表,名叫spt_values里面保存了一些常数,当类型为p 时,为自然数. 你执行一下: select * from master..spt_values 就不难理解了.
insert into tb select '123','8,9,20'
insert into tb select 'as','23,24,25'
insert into tb select 'vv','16,17,18'
go
select a.a,substring(a.b,b.number,charindex(',',a.b+',',b.number+1)-b.number)b
from tb a,master..spt_values b
where b.type='p' and b.number<=len(a.b) and substring(a.b,b.number,1)<>',' and substring(','+a.b,b.number,1)=','
/*
a b
---------- --------------------
123 8
123 9
123 20
as 23
as 24
as 25
vv 16
vv 17
vv 18(9 行受影响)
*/
go
drop table tb
insert into #tb
select '123','8,9,20'
union all
select 'as','23,24,25'
union all
select 'vv','16,17,18'
select m.a,b=SUBSTRING(m.b,n.number,charindex(',',m.b+',',n.number)-n.number) from #tb m,master..spt_values n
where n.type='p' and n.number<=LEN(m.b) and CHARINDEX(',',','+m.b,n.number)=n.number
a b
---------- --------------------
123 8
123 9
123 20
as 23
as 24
as 25
vv 16
vv 17
vv 18
标题:分拆列值1
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间: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, 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. 新的解决方法(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 行受影响)
*/
你执行一下:
select * from master..spt_values
就不难理解了.