将数据表中某列查询出来的结果用逗号隔开
/******************************************** 将数据表中某列查询出来的结果用逗号隔开
*******************************************/
Create Function [dbo].[f_GetCollateralName]
(
@CollateralIds Varchar(Max)
)
Returns Varchar(Max)
As
Begin
Declare @thisResult Varchar(Max);
Select @thisResult = Coalesce(@thisResult, '') + Cast(CollateralName As Varchar(Max))
+ ','
From BIS_Collateral
Where CollateralId In (Select col
From dbo.f_SplitStr(@CollateralIds, ','));
Set @thisResult = Isnull(@thisResult, '');
Set @thisResult = Substring(@thisResult, 0, Len(@thisResult));
Return @thisResult;
End
/*******************************************
* 调用示例
*******************************************/
Select a.*,dbo.f_GetCollateralName(a.Collateral) CollateralName From BIS_Product a
另附:
辅助函数:
/*******************************************
* 将某包含多个相同字符的字符串查询为表
*******************************************/
CREATE function [dbo].[f_SplitStr]
(
@SourceSql nvarchar(max),--源分隔字符串
@StrSeprate varchar(10)--分隔符
)
returns @temp table(col nvarchar(max))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
分享到: