Sql function 多行中的列合並為一行一列的方法

復制代碼 代碼如下:
CREATE TABLE tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),Reason varchar(50))
insert into tb values('55','279','4','物量積壓','加工人員設備不足;T排制作進度較慢;')
insert into tb values('55','279','4','物量積壓','部件人員不足;')
insert into tb values('55','279','4','物量積壓','跨間場地積壓;圖紙問題較多;')
insert into tb values('56','300','4','物量積壓','AAAA;')
insert into tb values('56','300','4','物量積壓','BBBB;')
insert into tb values('56','300','4','物量積壓','CCCC;')

create function test(@standards varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(500)
set @re = ''
select @re = @re+','+Reason
from tb
where @standards=standards
return (stuff(@re,1,1,''))
end

調用
復制代碼 代碼如下:
select distinct standards,amount,variation,statuss,Reason = dbo.test('55') from tb where standards=55
select distinct standards,amount,variation,statuss,Reason = dbo.test('56') from tb where standards=56