有两个表:
A(表)
A_id A_content(列)
(如)1 "A_content1 "
2 "A_content2 "
3 "A_content3 "
. .
B(表)
B_id A_id B_Content(列)
(如)1 1 "B_Content1 "
2 1 "B_Content2 "
3 1 "B_Content3 "
4 2 "B_Content4 "
5 2 "B_Content5 "
6 3 "B_Content6 "
7 3 "B_Content7 "
共有字段是A_id
我现在要得到
A_content R_Content(列)
A_content1 B_Content1+B_Content2+B_Content3
A_content2 B_Content4+B_Content5
A_content3 B_Content6+B_Content7
create table A(A_id int,A_content varchar(50))
insert A select 1, 'A_content1 '
union all select 2, 'A_content2 '
union all select 3, 'A_content3 '
create table B(B_id int,A_id int,B_Content varchar(50))
insert B select 1,1 , 'B_Content1 '
union all select 2,1 , 'B_Content2 '
union all select 3,1 , 'B_Content3 '
union all select 4,2 , 'B_Content4 '
union all select 5,2 , 'B_Content5 '
union all select 6,3 , 'B_Content6 '
union all select 7,3 , 'B_Content7 '
--建函数
create function test_b(@a_id varchar(20))
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql= ' '
select @sql=@sql+ '+ '+B_Content from b where a_id=@a_id
set @sql=stuff(@sql,1,1, ' ')
return (@sql)
end
select A_content,dbo.test_b(a_id)B_Content from a
A_content B_Content