create table A(id int, test varchar(20)) insert A select 1, 'MS' union all select 1, 'SQL' union all select 1, 'Server' union all select 2, 'I' union all select 2, 'am' union all select 2, 'SQL' union all select 2, 'OK'create function fun(@id int) returns varchar(1000) as begin declare @re varchar(1000) set @re='' select @re=@re+test+' ' from A where id=@id return @re endselect distinct id, dbo.fun(id) as test from Aid test ----------- ------------------------- 1 MS SQL Server 2 I am SQL OK (2 row(s) affected)
LS的发现你的少了两个GO,在2000里运行不了的.. create table A(id int, test varchar(20)) insert A select 1, 'MS' union all select 1, 'SQL' union all select 1, 'Server' union all select 2, 'I' union all select 2, 'am' union all select 2, 'SQL' union all select 2, 'OK' go create function fun(@id int) returns varchar(1000) as begin declare @re varchar(1000) set @re='' select @re=@re+test+' ' from A where id=@id return @re end go select distinct id, dbo.fun(id) as test from Aid test ----------- ------------------------- 1 MS SQL Server 2 I am SQL OK (2 row(s) affected)
create table A(RID int, test varchar(20)) insert into A values(1, 'MS') insert into A values(1, 'SQL') insert into A values(1, 'Server') insert into A values(2, 'I') insert into A values(2, 'am') insert into A values(2, 'SQL') insert into A values(2, 'OK') select a.RID, (IsNull((select top 1 test from A where RID = a.RID), ' ')) + ' ' + (IsNull((select top 1 test from A where RID = a.RID and test not in (select top 1 test from A where RID = a.RID)), ' ')) + ' ' + (IsNull((select top 1 test from A where RID = a.RID and test not in (select top 2 test from A where RID = a.RID)), ' ')) + ' ' + (IsNull((select top 1 test from A where RID = a.RID and test not in (select top 3 test from A where RID = a.RID)), ' ')) as test1 from A a group by a.RID 有个限制 test针对每个id群组最大记录数要确定(这里按表中最大为4)
insert A select 1, 'MS'
union all select 1, 'SQL'
union all select 1, 'Server'
union all select 2, 'I'
union all select 2, 'am'
union all select 2, 'SQL'
union all select 2, 'OK'create function fun(@id int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+test+' ' from A where id=@id return @re
endselect distinct id, dbo.fun(id) as test from Aid test
----------- -------------------------
1 MS SQL Server
2 I am SQL OK (2 row(s) affected)
create table A(id int, test varchar(20))
insert A select 1, 'MS'
union all select 1, 'SQL'
union all select 1, 'Server'
union all select 2, 'I'
union all select 2, 'am'
union all select 2, 'SQL'
union all select 2, 'OK'
go
create function fun(@id int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+test+' ' from A where id=@id return @re
end
go
select distinct id, dbo.fun(id) as test from Aid test
----------- -------------------------
1 MS SQL Server
2 I am SQL OK (2 row(s) affected)
insert into A values(1, 'MS')
insert into A values(1, 'SQL')
insert into A values(1, 'Server')
insert into A values(2, 'I')
insert into A values(2, 'am')
insert into A values(2, 'SQL')
insert into A values(2, 'OK')
select a.RID, (IsNull((select top 1 test from A where RID = a.RID), ' '))
+ ' ' +
(IsNull((select top 1 test from A where RID = a.RID and
test not in (select top 1 test from A where RID = a.RID)), ' '))
+ ' ' +
(IsNull((select top 1 test from A where RID = a.RID and
test not in (select top 2 test from A where RID = a.RID)), ' '))
+ ' ' +
(IsNull((select top 1 test from A where RID = a.RID and
test not in (select top 3 test from A where RID = a.RID)), ' '))
as test1
from A a group by a.RID
有个限制 test针对每个id群组最大记录数要确定(这里按表中最大为4)