Create function addstr(@A varchar(12)) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+Cast(name as varchar) from table1 where @A=type return(@str) end;select distinct type,dbo.addstr([name]) as name from table1
create table TABLE1 ( type varchar(10), name varchar(10) ) create table TABLE2 ( type varchar(10), name varchar(100) ) insert TABLE1 select 'a','1' union select 'a','2' union select 'a','3' union select 'b','4' union select 'b','5' union select 'c','6' union select 'c','7' go--创建函数 create function f_str(@type varchar(10)) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+name from TABLE1 where type=@type return @str end go --插入 insert TABLE2 select type,dbo.f_str(type) from TABLE1 group by type--查看 select * from TABLE2--删除测试环境 drop function f_str drop table TABLE1,TABLE2--结果 /*type name ---------- ------------ a 123 b 45 c 67(3 row(s) affected) */
新建个函数: Create function addstr(@A varchar(12)) returns varchar(8000) as declare @str varchar(8000) begin
set @str='' select @str=@str+Cast(name as varchar) from table1 where @A=type return(@str) end;select distinct type,dbo.addstr([name]) as name from table1
稿錯了,應該這樣 create table tt(type varchar(2),name varchar(2)) Insert into tt select 'a','1' union all select 'a','2' union all select 'a','3' union all select 'b','4' union all select 'b','5' union all select 'c','6' union all select 'c','7'--函數 create function dbo.fn_b(@a varchar(10)) returns varchar(1000) as begin declare @s varchar(1000) set @s='' select @s=@s+[name] from tt where type=@a return (@s) end--刪除 drop table tt drop function dbo.fn_bselect type,name=dbo.fn_b(type) from tt group by type--結果 type name ---------------------- a 123 b 45 c 67
returns varchar(8000)
as
begin
declare
@str varchar(8000)
set @str=''
select @str=@str+Cast(name as varchar) from table1
where @A=type
return(@str)
end;select distinct type,dbo.addstr([name]) as name from table1
(
type varchar(10),
name varchar(10)
)
create table TABLE2
(
type varchar(10),
name varchar(100)
)
insert TABLE1
select 'a','1' union
select 'a','2' union
select 'a','3' union
select 'b','4' union
select 'b','5' union
select 'c','6' union
select 'c','7'
go--创建函数
create function f_str(@type varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+name from TABLE1 where type=@type
return @str
end
go
--插入
insert TABLE2
select type,dbo.f_str(type) from TABLE1 group by type--查看
select * from TABLE2--删除测试环境
drop function f_str
drop table TABLE1,TABLE2--结果
/*type name
---------- ------------
a 123
b 45
c 67(3 row(s) affected)
*/
Create function addstr(@A varchar(12))
returns varchar(8000)
as
declare
@str varchar(8000)
begin
set @str=''
select @str=@str+Cast(name as varchar) from table1
where @A=type
return(@str)
end;select distinct type,dbo.addstr([name]) as name from table1
create table tt(type varchar(2),name varchar(2))
Insert into tt
select 'a','1'
union all select 'a','2'
union all select 'a','3'
union all select 'b','4'
union all select 'b','5'
union all select 'c','6'
union all select 'c','7'--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[name] from tt where type=@a
return (@s)
end--刪除
drop table tt
drop function dbo.fn_bselect type,name=dbo.fn_b(type) from tt
group by type--結果
type name
----------------------
a 123
b 45
c 67
只能当前数据有用
对该结构没有用