和以下类似--先建立个 function
CREATE function FC_1(@id varchar(100))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+rtrim(DTL)+'\' from table1 where id=@id
return substring(@str,1,len(@str)-1)
end
go
create table table1 (ID varchar(100),DTL varchar(100))
insert into table1 values ('001','test11')
insert into table1 values('001','test12')
insert into table1 values('001','test13')
insert into table1 values('002','test11')
insert into table1 values('002','test12')
insert into table1 values('002','test13')
insert into table1 values('003','test11')
insert into table1 values ('003','test12')
insert into table1 values('003','test13')
insert into table1 values('003','test14')
insert into table1 values('005','test11')
select distinct id,dbo.fc_1(id) from table1
drop table table1
drop function FC_1
CREATE function FC_1(@id varchar(100))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+rtrim(DTL)+'\' from table1 where id=@id
return substring(@str,1,len(@str)-1)
end
go
create table table1 (ID varchar(100),DTL varchar(100))
insert into table1 values ('001','test11')
insert into table1 values('001','test12')
insert into table1 values('001','test13')
insert into table1 values('002','test11')
insert into table1 values('002','test12')
insert into table1 values('002','test13')
insert into table1 values('003','test11')
insert into table1 values ('003','test12')
insert into table1 values('003','test13')
insert into table1 values('003','test14')
insert into table1 values('005','test11')
select distinct id,dbo.fc_1(id) from table1
drop table table1
drop function FC_1
只用查询语句,因为我要通过一条查询语句查出关联的数据,基中"姓名"是由一个库中的两个UserID组成的
returns varchar(8000)
as
begindeclare @tmp varchar(8000),@get varchar(8000)DECLARE my_cursor CURSOR FOR
SELECT name from table1 where infoid=@infoidOPEN my_cursorFETCH NEXT FROM my_cursor into @getset @tmp = ltrim(rtrim(@get))WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor into @get
set @tmp = @tmp + ',' + ltrim(rtrim(@get))
ENDclose my_cursor
DEALLOCATE my_cursorreturn(@tmp)
endselect distinct infoid,dbo.uf_name(infoid) from user group by infoid where infoid in(select infoid from info)