--分隔字串ALTER function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql"") begin set @ch=left(@SourceSql,charindex(",",@SourceSql,1)-1) insert @temp values(@ch) set @SourceSql=stuff(@SourceSql,1,charindex(",",@SourceSql,1),"") end return end GO--模仿下面的函式 (你的需要一個表連線查詢)Create FUNCTION JoinString --合併字串 多行合併為一行( @UserName varchar(50) ) RETURNS varchar(8000) AS BEGIN declare @Str varchar(8000) set @Str = "" select @Str = @Str +"," + ISNull(BuMenName,"") from ERPUserGuanliDept where UserName = @UserName if(@Str"") set @Str=substring(@Str,2,len(@Str)-1) return @Str END--使用時select distinct UserName,dbo.JoinString(UserName) as DeptList from ERPUserGuanliDept
--分隔字串ALTER function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql"") begin set @ch=left(@SourceSql,charindex(",",@SourceSql,1)-1) insert @temp values(@ch) set @SourceSql=stuff(@SourceSql,1,charindex(",",@SourceSql,1),"") end return end GO--模仿下面的函式 (你的需要一個表連線查詢)Create FUNCTION JoinString --合併字串 多行合併為一行( @UserName varchar(50) ) RETURNS varchar(8000) AS BEGIN declare @Str varchar(8000) set @Str = "" select @Str = @Str +"," + ISNull(BuMenName,"") from ERPUserGuanliDept where UserName = @UserName if(@Str"") set @Str=substring(@Str,2,len(@Str)-1) return @Str END--使用時select distinct UserName,dbo.JoinString(UserName) as DeptList from ERPUserGuanliDept