给个例子给你: 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
declare @s varchar(100) set @s='' select @s=@s+','+fieldname from tablename group by fieldnameif @s<>'' set @s=stuff(@s,1,1,'')print @s
上面的是两个表匹配之后,再将匹配结果合成列的过程,如题的效果更简单些。你自己稍做变动吧! =-------------------------------------------------- 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 行)
可以自行加上Distinct去重复。
declare @s varchar(8000) set @s = '' select @s = @S + ',' + col from tab group by col-----select distinct @S = @s + ',' + col from tab-- select @s = stuff(@s , 1, 1, '')
楼主的问题不太明白 第一个问题是要将某一列的重复值去掉,这个有很多解决办法,最简单的就是用"Distinct 列名"来实现 第二个问题就不明白了,是要把过滤后的所有行内容连接在一起,还是要将那些有重复列的某一列的值联系起来 如果是后者,需要这样来实现1.函数是必须的,借用1楼的函数(这个函数基本上是大家都会用的) CREATE 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 GO2.select Distinct 列名,dbo.getstr(列名) from 表名
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
set @s=''
select @s=@s+','+fieldname
from tablename
group by fieldnameif @s<>''
set @s=stuff(@s,1,1,'')print @s
=--------------------------------------------------
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 行)
set @s = ''
select @s = @S + ',' + col from tab
group by col-----select distinct @S = @s + ',' + col from tab--
select @s = stuff(@s , 1, 1, '')
第一个问题是要将某一列的重复值去掉,这个有很多解决办法,最简单的就是用"Distinct 列名"来实现
第二个问题就不明白了,是要把过滤后的所有行内容连接在一起,还是要将那些有重复列的某一列的值联系起来
如果是后者,需要这样来实现1.函数是必须的,借用1楼的函数(这个函数基本上是大家都会用的)
CREATE 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
GO2.select Distinct 列名,dbo.getstr(列名) from 表名