如果建个函数就好处理了
create function GetContent
(@ID int)
AS
Begin
Declare @sql nvarchar(4000)
select @sql=@sql+','+content from 表 where id=@id
return @sql
End select distinct id,dbo.GetContent(id) from 表
create function GetContent
(@ID int)
AS
Begin
Declare @sql nvarchar(4000)
select @sql=@sql+','+content from 表 where id=@id
return @sql
End select distinct id,dbo.GetContent(id) from 表
select top 3A.idno,A.content+';'+B.content from test A
left join test B on A.idno=B.idno and A.content!=B.content
select top 3 A.ID,A.content+';'+B.content from test A
left join test B on A.ID=B.ID and A.content!=B.content
你的写法明显不对,你的结果是:
ID content
1 aa,dd
1 dd,aa
2 bb,ee
2 ee,bb
....以下类似,明显不对!!
create table t(ID int,content varchar(20))
insert into t select 1,'aa'
insert into t select 2,'bb'
insert into t select 3,'cc'
insert into t select 1,'dd'
insert into t select 2,'ee'
insert into t select 3,'ff'
go--创建用户定义函数
create function f_str(@ID int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + content from t where id=@id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行查询
select ID,content=dbo.f_str(ID) from t group by ID--输出结果
ID content
---- -------
1 aa,dd
2 bb,ee
3 cc,ff
on a.id=b.id and a.content!=b.content
创建一个函数,然后调用就可以啦create function f1(@a int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+content from table1 where id=@a
return(stuff(@r,1,1,''))
end
go
--调用实现查询
select id,content=dbo.f1(id) from table1 group by id