ID int
company_id int
morp bit
product nvarchar(50)create function f_1(@company_id,@morp bit)
return varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+','+product from tb1 where company_id=@company_id and morp=@morp
return stuff(@s,1,1,'')
end
go
select company_id,morp,product=dbo.f_1(company_id,morp) from tb1 group by company_id,morp
company_id int
morp bit
product nvarchar(50)create function f_1(@company_id,@morp bit)
return varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+','+product from tb1 where company_id=@company_id and morp=@morp
return stuff(@s,1,1,'')
end
go
select company_id,morp,product=dbo.f_1(company_id,morp) from tb1 group by company_id,morp
[create] table tb1
(ID int,company_id int,morp bit,product nvarchar(50))insert tb1 values (1,1,0,'原料一')
insert tb1 values (2,1,1,'产品一')
insert tb1 values (3,1,1,'产品二')
insert tb1 values (4,1,1,'产品三')
insert tb1 values (5,2,0,'原料二')
insert tb1 values (6,2,0,'原料三')
insert tb1 values (7,2,1,'产品四')
go
[create] table company
(company_id int,product varchar(50),material varchar(50))
go
create function f_he(@col1 int,@col2 int)
returns varchar(2000)
as
begin
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+','+product from tb1 where company_id=@col1 and morp=@col2
return(stuff(@sql,1,1,''))
end
goinsert company
select a.company_id,a.p_m as product,b.p_m as material
from
(
select company_id,morp,dbo.f_he(company_id,morp) as p_m from tb1 where morp=1 group by company_id,morp
) a inner join
(
select company_id,morp,dbo.f_he(company_id,morp) as p_m from tb1 where morp=0 group by company_id,morp
) b on a.company_id=b.company_idselect * from companydrop function f_he
drop table tb1
drop table company
company_id product material
----------- -------------------------------------------------- --------------------------------------------------
1 产品一,产品二,产品三 原料一
2 产品四 原料二,原料三(所影响的行数为 2 行)