学步园

--关于新方法解决字符串替换和拆分问题的总结
-->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 all
select 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    AA
1    AA    BB
1    AA    CC
2    BB    FF
2    BB    EE
2    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 all
select '02','b' union all
select '03','c' union all
select '04','d' union all
select '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 all
select 2,'02' union all
select 3,'01,03' union all
select 4,'02,05' union all
select 5,'01,02,03' union all
select 6,'01,02,04,05' union all
select 7,'02,04'
go

with t
as(
select 
    b.id,
    a.内容 
from 
    [B2] b
inner join 
    [A1] a
on 
    CHARINDEX(a.编码,b.内容)>0
)
select 
    a.id,
    内容=stuff((SELECT ','+内容 
from 
    t 
where 
    a.id=t.id for xml path('')),1,1,'')
from 
    t  a
group by 
    a.id
/*
id    内容
----------------------
1    a,e
2    b
3    a,c
4    b,e
5    a,b,c
6    a,b,d,e
7    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 Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go

--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select 
    top 100 ID=Identity(int,1,1) into #Num 
from 
    syscolumns a,
    syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,
    #Num b
where
    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.Col2
from 
    (
        select 
            Col1,
            COl2=convert(xml,'<root><v>'
                +replace(COl2,',','</v><v>')+'</v></root>') 
        from Tab
    )a
outer apply
    (
        select 
            Col2=C.v.value('.','nvarchar(100)') 
        from 
            a.COl2.nodes('/root/v')C(v)
    )b





/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/