请问拆分列值的方法

请教拆分列值的方法？
请教拆分列值的方法？
表的的格式如下:
id vt
----------- --------------
1 1,2ab,c3,4eft
2 1a,3,B
想要得到的拆分的结果：
id vt
----------- ------
1 1
1 2ab
1 c3
1 4eft
2 1a
2 3
2 B
如何写比较合适？
建表语句已写好，可直接复制：

SQL code

IF OBJECT_ID('tb') IS NOT NULL    DROP TABLE tb;GOCREATE TABLE tb( id INT identity(1,1),  vt VARCHAR(500));GOINSERT INTO tb(vt) VALUES('1,2ab,c3,4eft');INSERT INTO tb(vt) VALUES('1a,3,B');select * from tb;

[解决办法]
字符串分隔函数
http://blog.csdn.net/xiaoyuan402/article/details/7049473
[解决办法]
create table t1
(
id int,
vt varchar(50)
)
insert into t1
select 1, '1,2ab,c3,4eft' union all
select 2, '1a,3,B'
select * from t1

select a.id,SUBSTRING(vt,number,CHARINDEX(',',vt+',',number)-number) as vt
from t1 as a with(nolock) inner join master..spt_values as b with(nolock)
on number<=DATALENGTH(vt)+1 and SUBSTRING(','+vt,number,1)=',' and b.type='P'

--------------------------
idvt
11
12ab
1c3
14eft
21a
23
2B
[解决办法]

SQL code

--分拆列值--原著：邹建--改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳/*有表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) )B drop table tb /* id          value ----------- ------------------------------ 1          aa 1          bb 2          aaa 2          bbb 2          ccc (5 行受影响) */
[解决办法]
探讨


楼上的很好，
晚点结贴，想再收集点意见

[解决办法]
SQL code
-- 用with语句;with t as(    select         id,         vt=(case when charindex(',',vt)>0 then substring(vt, charindex(',',vt)+1, len(vt)) else '' end),         st=(case when charindex(',',vt)>0 then left(vt, charindex(',',vt)-1) else vt end)     from tb    union all    select         t.id,         vt=(case when charindex(',',t.vt)>0 then substring(t.vt, charindex(',',t.vt)+1, len(t.vt)) else '' end),         st=(case when charindex(',',t.vt)>0 then left(t.vt, charindex(',',t.vt)-1) else t.vt end)     from t    where len(t.vt)>0)select id, st from torder by id 

 

查看更多 下一篇