/*
标题：分拆列值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)
)B

DROP TABLE tb

/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
*/

------解决方案--------------------
合并分拆表_整理贴1
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

------解决方案--------------------
SQL code

if object_id('t1') is not null 
    drop table t1
Go
Create table t1([SIC] nvarchar(3),[AC] nvarchar(5),[DESCRIP] nvarchar(10),[OWNERS] nvarchar(20))
Insert into t1
Select 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 #tmp
SELECT SIC,AC,DESCRIP,OWNERS+',' AS OWNERS INTO #tmp FROM t1



WHILE 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,OWNERS


DROP TABLE #tmp