--?? declare @test table ( a varchar(20) ) insert into @test select 'a1' union all select 'a2' union all select 'a3' union all select 'a4' union all select 'a5'declare @b varchar(20) select @b='' select @b=@b+a from @test select @b/* a1a2a3a4a5or you can use a function if you need group it */
use pubs if exists(select * from sysobjects where name='ta') drop table ta if exists(select * from sysobjects where name='tb') drop table tb gocreate table ta(pid int ,name char(10)) insert ta select 1,'aa' create table tb(pid int,cid int) insert tb select 1,2 union all select 1,5 union all select 1,6select * from ta select * from tb goCREATE FUNCTION getStr(@pid int) returns varchar(100) as begin declare @strAll varchar(100) set @strAll='' select @strAll=@strAll+','+cast(cid as varchar(10)) from tb where pid=@pid return stuff(@strAll,1,1,'') end GOselect a.pid,[name],dbo.getstr(b.pid) from ta a inner join tb b on a.pid=b.pid order by a.piddrop function getStr drop table ta,tb
上面数据演示的结果:pid name ----------- ---------- 1 aa (所影响的行数为 1 行)pid cid ----------- ----------- 1 2 1 5 1 6(所影响的行数为 3 行)--结果-- pid name ----------- ---------- ---------------------------------------------------------------------------------------------------- 1 aa 2,5,6 1 aa 2,5,6 1 aa 2,5,6(所影响的行数为 3 行)
declare @test table
(
a varchar(20)
)
insert into @test
select 'a1' union all
select 'a2' union all
select 'a3' union all
select 'a4' union all
select 'a5'declare @b varchar(20)
select @b=''
select @b=@b+a from @test
select @b/*
a1a2a3a4a5or you can use a function if you need group it
*/
if exists(select * from sysobjects where name='ta') drop table ta
if exists(select * from sysobjects where name='tb') drop table tb
gocreate table ta(pid int ,name char(10))
insert ta select 1,'aa'
create table tb(pid int,cid int)
insert tb select 1,2
union all select 1,5
union all select 1,6select * from ta
select * from tb
goCREATE FUNCTION getStr(@pid int)
returns varchar(100)
as
begin
declare @strAll varchar(100)
set @strAll=''
select @strAll=@strAll+','+cast(cid as varchar(10)) from tb where pid=@pid
return stuff(@strAll,1,1,'')
end
GOselect a.pid,[name],dbo.getstr(b.pid)
from ta a inner join tb b on a.pid=b.pid
order by a.piddrop function getStr
drop table ta,tb
----------- ----------
1 aa (所影响的行数为 1 行)pid cid
----------- -----------
1 2
1 5
1 6(所影响的行数为 3 行)--结果--
pid name
----------- ---------- ----------------------------------------------------------------------------------------------------
1 aa 2,5,6
1 aa 2,5,6
1 aa 2,5,6(所影响的行数为 3 行)