create table A
(
no int,
name varchar(10)
)
insert A
select 1,'a' union
select 1,'b' union
select 1,'c' union
select 1,'d' union
select 2,'a' union
select 2,'b' union
select 2,'c' union
select 3,'d'
go--创建函数
create function f_str(@no int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+' '+name from A where no=@no
return stuff(@str,1,1,'')
end
go--查询
select no
,dbo.f_str(no) as 'name'
from A
group by no--删除测试环境
drop function f_str
drop table A--结果
/*
no name
----------- -----------
1 a b c d
2 a b c
3 d(3 row(s) affected)
*/
(
no int,
name varchar(10)
)
insert A
select 1,'a' union
select 1,'b' union
select 1,'c' union
select 1,'d' union
select 2,'a' union
select 2,'b' union
select 2,'c' union
select 3,'d'
go--创建函数
create function f_str(@no int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+' '+name from A where no=@no
return stuff(@str,1,1,'')
end
go--查询
select no
,dbo.f_str(no) as 'name'
from A
group by no--删除测试环境
drop function f_str
drop table A--结果
/*
no name
----------- -----------
1 a b c d
2 a b c
3 d(3 row(s) affected)
*/
create table t (
no int,
name varchar(50)
)insert into t select 1,'a'
union select 1,'b'
union select 1,'c'
union select 1,'d'
union select 2,'a'
union select 2,'b'
union select 2,'c'
union select 3,'d'
go---建立函数
create function f_str(@no int)
returns varchar(8000)
AS
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+' '+rtrim(name) from t where no = @no
set @ret = case when len(@ret)>0 then stuff(@ret,1,1,'') else @ret end
return @ret
end--查询Select no,dbo.f_str(no)as name from t group by no
--扫尾
drop table t
drop function f_str---结果no name
1 a b c d
2 a b c
3 d
create function f_str(@no int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+' '+name from A where no=@no
return stuff(@str,1,1,'')
end
go--查询
select no
,dbo.f_str(no) as 'name'
from A
group by no
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + name from tb where no = @no
return @str
end
gocreate table tb
(
no int,
name varchar(50)
)
insert tb
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 2,'a' union all
select 2,'b' union all
select 2,'c' union all
select 3,'c'
select no,dbo.fun(no) as name from tb group by no
drop function fun
drop table tb