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)
*/
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
create function fun(@no int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + name from tb where no = @no
return @str
end
go--创建表
create 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
--结果
/*
no name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 abcd
2 abc
3 c（所影响的行数为 3 行）*/
insert into a select 1,'a'
insert into a select 1,'b'
insert into a select 1,'c'
insert into a select 1,'d'
insert into a select 2,'a'
insert into a select 2,'b'
insert into a select 2,'c'
insert into a select 3,'a'
create table b (no int, name char(10))
declare
@a int,
@b varchar(10),
@c char(1)declare x cursor for ----双游标判断
select distinct no from aopen x
fetch next from x into @awhile(@@fetch_status=0)
begin
set @b=''
declare y cursor for
select name from a where no=@a
open y
fetch next from y into @c while (@@fetch_status=0)
begin
set @b=@b+@c fetch next from y into @c
end
close y
deallocate y
insert into b select @a,@b
fetch next from x into @a
endclose x
deallocate xselect *
from bdrop table a
drop table b---结果
---1 abcd
---2 abc
---3 a