Sql function 多行中的列歸並為一行一列的辦法。本站提示廣大學習愛好者:(Sql function 多行中的列歸並為一行一列的辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是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