数据库中字符串分解有关问题

数据库中字符串分解问题
1.表一中
jg_dm bz
001， 111
001，002， 125
003，004，005， 达到

如何转换成
jg_dm bz
001 111
001 125
002 125
003 达到
004 达到
005 达到


[解决办法]

SQL code

分拆列值原著：邹建改编：爱新觉罗.毓华  2007-12-16  广东深圳有表tb, 如下:id          value----------- -----------1           aa,bb2           aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id          value----------- --------1           aa1           bb2           aaa2           bbb2           ccc1. 旧的解决方法(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, # BWHERE 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')goSELECT A.id, B.valueFROM(    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb)AOUTER APPLY(    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v))BDROP TABLE tb/*id          value----------- ------------------------------1           aa1           bb2           aaa2           bbb2           ccc(5 行受影响)*/
[解决办法]
SQL code
create table tb(jg_dm varchar(20) , bz varchar(10))insert into tb values('001,'        ,   '111') insert into tb values('001,002,'    ,   '125') insert into tb values('003,004,005,',   '达到') go SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select * from(  SELECT jg_dm = SUBSTRING(A.jg_dm, B.id, CHARINDEX(',', A.jg_dm + ',', B.id) - B.id) , A.bz  FROM tb A, # B  WHERE SUBSTRING(',' + A.jg_dm, B.id, 1) = ',') twhere jg_dm <> ''DROP TABLE #,tb/*jg_dm                bz         -------------------- ---------- 001                  111001                  125002                  125003                  达到004                  达到005                  达到（所影响的行数为 6 行）*/
[解决办法]
探讨

--如果你每个jg_dm后都有个,号,如下: 

SQL codecreate table tb(jg_dm varchar(20) , bz varchar(10))
insert into tb values('001,'        ,   '111') 
insert into tb values('001,002,'    ,   '125') 
insert into tb values('003,004,005,',   '达到') 
go 

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

select * from
(
  SELECT jg_dm = SUBSTRING(A.jg_dm, B.id,…