if object_id('TableA') is not null drop table tableA
go
create table TableA
(
A1 varchar(3),
A2 varchar(4)
)
insert into TableA
select '001','深圳' union all
select '002','广州'
go
if object_id('TableB') is not null drop table tableB
go
create table TableB
(
B1 varchar(3),
B2 varchar(4)
)
insert into TableB
select '001','罗湖' union all
select '001','南山' union all
select '001','福田' union all
select '002','天河'
go
select *
from tableA A
OUTER APPLY(
SELECT B2 = STUFF(REPLACE(REPLACE((
SELECT v = B2 FROM TableB B
WHERE B1 = A.A1
FOR XML AUTO), '<B v="', ','), '"/>', ''), 1, 1, '')
)B
go
create table 表(A1 varchar(100),A2 varchar(100))
insert into 表 select '001','深圳'
insert into 表 select '002','广州'
Go
------------
create table 表1(B1 varchar(100),B2 varchar(100))
insert into 表1 select '001','罗湖'
insert into 表1 select '001','南山'
insert into 表1 select '001','福田'
insert into 表1 select '002','天河'
go
create function f_str(@B1 varchar(100))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+B2 from 表1 where B1 = @B1
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select A.* ,b.B2 from 表 A inner join
(select B1,B2=dbo.f_str(B1) from 表1 group by B1 ) B
on A.A1=B.B1
if object_id('TableA') is not null drop table tableA
go
create table TableA
(
A1 varchar(3),
A2 varchar(4)
)
insert into TableA
select '001','深圳' union all
select '002','广州'
go
if object_id('TableB') is not null drop table tableB
go
create table TableB
(
B1 varchar(3),
B2 varchar(4)
)
insert into TableB
select '001','罗湖' union all
select '001','南山' union all
select '001','福田' union all
select '002','天河'
go
create function dbo.f_test(@a varchar(3))
returns varchar(50)
as
begin
declare @b varchar(50)
set @b=''
select @b=@b+b2+','
from TableB
where B1=@a
set @b=left(@b,len(@b)-1)
return (@b)
end
GO
select a.a1,a.a2,b.b2
from tablea a
inner join
(select b1,dbo.f_test(b1)as b2 from tableB group by b1)b
on a.a1=b.b1