怎么拆分行_Sql Server

如何拆分行？
我的表格如下
SIC AC DESCRIP OWNERS
ECP CP_NC Checkpoint 355518,416371
ECP CPQ Quickcheck 355518, 457171,325442
我想拆分成

SIC AC DESCRIP OWNERS
ECP CP_NC Checkpoint 355518
ECP CP_NC Checkpoint 416371
ECP CPQ Quickcheck 355518
ECP CPQ Quickcheck 457171
ECP CPQ Quickcheck 325442

试了几个方法都不成功。求高手指点。

------解决方案--------------------

SQL code

/*标题：分拆列值1作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2008-11-20地点：广东深圳描述有表tb, 如下:id          value----------- -----------1           aa,bb2           aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id          value----------- --------1           aa1           bb2           aaa2           bbb2           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, # BWHERE 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')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 行受影响)*/
------解决方案--------------------
合并分拆表_整理贴1
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
------解决方案--------------------
SQL codeif object_id('t1') is not null     drop table t1GoCreate table t1([SIC] nvarchar(3),[AC] nvarchar(5),[DESCRIP] nvarchar(10),[OWNERS] nvarchar(20))Insert into t1Select N'ECP',N'CP_NC',N'Checkpoint',N'355518,416371'Union all Select N'ECP',N'CPQ',N'Quickcheck',N'355518,457171,325442'IF object_id('tempdb..#tmp') IS NOT NULL    DROP TABLE #tmpSELECT SIC,AC,DESCRIP,OWNERS+',' AS OWNERS INTO #tmp FROM t1WHILE EXISTS(SELECT 1 FROM #tmp WHERE CHARINDEX(',',OWNERS)>0)    BEGIN            INSERT INTO #tmp(SIC,AC,DESCRIP,OWNERS)            SELECT                     SIC,AC,DESCRIP,SUBSTRING(OWNERS,1,CHARINDEX(',',OWNERS)-1)                 FROM #tmp                WHERE CHARINDEX(',',OWNERS)>0                UPDATE #tmp                 SET OWNERS=STUFF(OWNERS,1,CHARINDEX(',',OWNERS),'')             WHERE CHARINDEX(',',OWNERS)>0    END    SELECT * FROM #tmp WHERE OWNERS>'' ORDER BY SIC,AC,DESCRIP,OWNERSDROP TABLE #tmp