mssql合并字符串

 --测试环境

create table tt(bh varchar(50),xh int,bwmc varchar(30))

insert into tt

select 'a',1,'aa'

union all select 'a',1,'bb'

union all select 'a',1,'cc'

union all select 'a',1,'aa'

union all select 'a',2,'d'

 

1.函数法

create function [dbo].[Getbwmc_str](@bh varchar(50),@xh int) returns varchar(2000) AS begin declare @str varchar(2000) set @str='' select @str=@str+','+rtrim(bwmc) from tt where bh = @bh and xh = @xh select @str=right(@str,len(@str)-1) where @str<>'' return @str end select bh,xh,dbo.getbwmc_str(bh,xh) from tt group by bh,xh

 

2.mssql2005专用法

(1)bwmc字符串直接相加,无论是否重复

select bh,xh,

bws=stuff((select ','+bwmc from tt  where bh=a.bh and xh=a.xh for xml path('')),1,1,'')

from tt a group by bh,xh

(2)bwmc字符串相加,不重复   //哈哈,这个是我自己想的!

select bh,xh,

bws=stuff((select ','+bwmc from (select distinct bwmc from tt where bh=a.bh and

xh=a.xh) o for xml path('')),1,1,'')

from tt a group by bh,xh

 

drop table tt

drop function dbo.Getbwmc_str