關於字串拆分，合并問題的整理

--關於新方法解決字串替換和拆分問題的總結-->TravyLee產生測試資料：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[CODE1] varchar(2),[CODE2] varchar(10))insert [test]select 1,'AA','AA BB CC' union allselect 2,'BB','FF EE DD'with T (id,[CODE1],P1,P2) as(    select         id,        [CODE1],        charindex(' ',' '+[CODE2]),        charindex(' ',[CODE2]+' ')+1     from         test    union all    select         a.id,        a.CODE1,        b.P2,        charindex(' ',[CODE2]+' ',b.P2)+1     from         test  a     join         T b     on         a.id=b.id     where         charindex(' ',[CODE2]+' ',b.P2)>0)select     a.id,    a.CODE1,    name=substring(a.[CODE2]+' ',b.P1,b.P2 - b.P1 - 1) from     test a join     T b on     a.id=b.id order by     1/*id    CODE1    name--------------------------1    AA    AA1    AA    BB1    AA    CC2    BB    FF2    BB    EE2    BB    DD*/--> 測試資料：[A1]if object_id('[A1]') is not null drop table [A1]create table [A1]([編碼] varchar(2),[內容] varchar(1))insert [A1]select '01','a' union allselect '02','b' union allselect '03','c' union allselect '04','d' union allselect '05','e'--> 測試資料：[B2]if object_id('[B2]') is not null drop table [B2]create table [B2]([id] int,[內容] varchar(11))insert [B2]select 1,'01,05' union allselect 2,'02' union allselect 3,'01,03' union allselect 4,'02,05' union allselect 5,'01,02,03' union allselect 6,'01,02,04,05' union allselect 7,'02,04'gowith tas(select     b.id,    a.內容 from     [B2] binner join     [A1] aon     CHARINDEX(a.編碼,b.內容)>0)select     a.id,    內容=stuff((SELECT ','+內容 from     t where     a.id=t.id for xml path('')),1,1,'')from     t  agroup by     a.id/*id    內容----------------------1    a,e2    b3    a,c4    b,e5    a,b,c6    a,b,d,e7    b,d*//*整理人：中國風(Roy) 日期:2008.06.06 */--> --> (Roy)產生測試數據 if not object_id('Tab') is null     drop table Tab Go Create table Tab([Col1] int,[Col2] nvarchar(1)) Insert Tab select 1,N'a' union all select 1,N'b' union all select 1,N'c' union all select 2,N'd' union all select 2,N'e' union all select 3,N'f' Go --合并表: --SQL2000用函數: go if object_id('F_Str') is not null     drop function F_Str go create function F_Str(@Col1 int) returns nvarchar(100) as begin     declare @S nvarchar(100)     select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1     return @S end go Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go --SQL2005用XML: --方法1: select     a.Col1,    Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') from     (select distinct COl1 from Tab) a Cross apply     (select         COl2=(select N','+Col2 from Tab where Col1=a.COl1                 For XML PATH(''), ROOT('R'), TYPE))b --方法2: select     a.Col1,    COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) from     (select distinct COl1 from Tab) a cross apply     (    select         Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)                 .query(' <Tab>                 {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")}                 {concat("",string(/Tab[last()]/@COl2))}                 </Tab>')     )b --SQL05用CTE: ;with roy as(    select         Col1,        Col2,        row=row_number()over(partition by COl1 order by COl1)     from         Tab        ) ,Roy2 as (select     COl1,    cast(COl2 as nvarchar(100))COl2,row from     Roy where     row=1 union all select     a.Col1,    cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from     Roy a join     Roy2 b on     a.COl1=b.COl1 and a.row=b.row+1    ) select     Col1,    Col2 from     Roy2 a where     row=(        select             max(row)         from             roy         where Col1=a.COl1        ) order by     Col1 option (MAXRECURSION 0) 產生結果: /* Col1        COl2 ----------- ------------ 1          a,b,c 2          d,e 3          f (3 行受影響) */ --> --> (Roy)產生測試數據 if not object_id('Tab') is null    drop table TabGoCreate table Tab([Col1] int,[COl2] nvarchar(5))Insert Tabselect 1,N'a,b,c' union allselect 2,N'd,e' union allselect 3,N'f'Go--SQL2000用輔助表:if object_id('Tempdb..#Num') is not null    drop table #Numgoselect     top 100 ID=Identity(int,1,1) into #Num from     syscolumns a,    syscolumns bSelect     a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from     Tab a,    #Num bwhere    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','--2000不使用輔助表Select    a.Col1,    COl2=substring(a.Col2,b.number,            charindex(',',a.Col2+',',b.number)-b.number) from     Tab a join     master..spt_values  b ON     B.type='p'     AND B.number BETWEEN 1 AND LEN(A.col2)where     substring(','+a.COl2,b.number,1)=','--Xml方法select     a.COl1,b.Col2from     (        select             Col1,            COl2=convert(xml,'<root><v>'                +replace(COl2,',','</v><v>')+'</v></root>')         from Tab    )aouter apply    (        select             Col2=C.v.value('.','nvarchar(100)')         from             a.COl2.nodes('/root/v')C(v)    )b/*Col1        COl2----------- -----1           a1           b1           c2           d2           e3           f*/