declare @str varchar(8000)
set @str=''
select @str=@str+name2+'\' from 你的表
set @str=left(@str,len(@str)-1)
select id,name,@str as name2 from 你的表 group by id,name
create function F_test2 (@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+name2+'\' from 你的表 where id=@id
set @str=left(@str,len(@str)-1)
return @str
end
调用函数:
select id,name,dbo.F_test2(id) from 你的表 group by id,name
declare @str varchar(8000)
set @str=''
select @str=@str+name2+'\' from 你的表
set @str=left(@str,len(@str)-1)
select id,name,@str as name2 from #test group by id,name
---------------------------
若id有多个不同值,则用函数:
create function F_test2 (@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+name2+'\' from 你的表 where id=@id
set @str=left(@str,len(@str)-1)
return @str
end
调用:
select id,name,dbo.F_test2(id) from 你的表 group by id,name
--建表
create table tb (A varchar(10),B varchar(1000))
Insert into tb
select '111','AA0000,AA0001,AA0002,'
union all select '111','BB0000,BB0001,BB0002,'
union all select '222','AA0120,AA0102,AA1453,'
union all select '222','BB1212,BB3423,BB2123,'
select * from tb
--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end
--刪除
drop table tb
drop function dbo.fn_b
--結果
select A,B=dbo.fn_b(A) from tb group by A
A B
-------------------------------------------
111 AA0000,AA0001,AA0002,BB0000,BB0001,BB0002,
222 AA0120,AA0102,AA1453,BB1212,BB3423,BB2123,