--和 vivianfdlpw() 基本上相同, 必须定义一个函数代码如下:
create table A
(
id varchar(10)
)
create table B
(
id varchar(10),char varchar(10)
)
insert A select 'A' union all select 'B' union all select 'C' union all select 'D'
insert B
select 'A','1' union all
select 'A','a' union all
select 'B','b' union all
select 'C','a' union all
select 'C','d' union all
select 'C','e'
create function f_string(@arg varchar(10))
returns varchar(100)
begin
declare @rtn varchar(100)
set @rtn = ''
select @rtn = @rtn + ',' + b.char
from B
where B.ID = @arg
if len(@rtn) > 0
set @rtn = stuff(@rtn,1,1, '')
return @rtn
end
select distinct A.ID as ID, dbo.f_string(A.ID) as '对应值' from A,B where A.ID *= B.ID
drop table A
drop table B
drop function f_string
create table A
(
id varchar(10)
)
create table B
(
id varchar(10),[char] varchar(10)
)
insert A select 'A' union select 'B' union select 'C' union select 'D'
insert B
select 'A','1' union
select 'A','a' union
select 'B','b' union
select 'C','a' union
select 'C','d' union
select 'C','e'
go
if exists(select 1 from sysobjects where id=object_id('f_str') and xtype='FN')
drop function f_str
go
create function f_str(@ID varchar(10))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + [char] from B where ID = @ID
set @ret = case when len(@ret)=0 then '' else stuff(@ret,1,1,'') end
return @ret
end
go
--测试
select A.id,[char]=dbo.f_str(A.id)
from A
left join B on A.id=B.id
group by A.id
--删除测试环境
drop table A,B
--结果
/*
id char
---------- ------------
A 1,a
B b
C a,d,e
D
create function f_str(@ID varchar(10))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + [char] from B where ID = @ID
set @ret = case when len(@ret)=0 then '' else stuff(@ret,1,1,'')