--创建策划试环境
create table A(ID int,pid int,value varchar(10))
create table B(pid int,name varchar(10))
insert A
select 1,2,'a' union
select 2,2,'b' union
select 3,4,'a' union
select 4,4,'b'
insert B select 2,'FIRST' union select 4,'SECOND'
go
if exists(select 1 from sysobjects where id=object_id('dbo.getstr') and xtype='FN')
drop function dbo.getstr
go
create function getstr
(
@pid int
)
returns varchar(20)
as
begin
declare @str varchar(20)
set @str=''
select @str=@str+','+value from A where pid=@pid
return stuff(@str,1,1,'')
end
go--测试
declare @tb table(ID int identity,Name varchar(20),value varchar(20))
insert @tb(name,value)
select distinct B.Name,[VALUE]=dbo.getstr(A.pid) from A join B on A.pid=B.pidselect * from @tb--删除测试环境
drop table A,B --结果
/*ID Name value
----------- -------------------- --------------------
1 FIRST a,b
2 SECOND a,b(所影响的行数为 2 行)
*/
create table A(ID int,pid int,value varchar(10))
create table B(pid int,name varchar(10))
insert A
select 1,2,'a' union
select 2,2,'b' union
select 3,4,'a' union
select 4,4,'b'
insert B select 2,'FIRST' union select 4,'SECOND'
go
if exists(select 1 from sysobjects where id=object_id('dbo.getstr') and xtype='FN')
drop function dbo.getstr
go
create function getstr
(
@pid int
)
returns varchar(20)
as
begin
declare @str varchar(20)
set @str=''
select @str=@str+','+value from A where pid=@pid
return stuff(@str,1,1,'')
end
go--测试
declare @tb table(ID int identity,Name varchar(20),value varchar(20))
insert @tb(name,value)
select distinct B.Name,[VALUE]=dbo.getstr(A.pid) from A join B on A.pid=B.pidselect * from @tb--删除测试环境
drop table A,B --结果
/*ID Name value
----------- -------------------- --------------------
1 FIRST a,b
2 SECOND a,b(所影响的行数为 2 行)
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货