--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert 表 select 'A','X','n'
union all select 'A','X','v'
union all select 'A','X','a'
union all select 'A','Y','t'
union all select 'A','Y','j'
go--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
go--删除测试
drop table 表
drop function f_str/*-测试结果col1 col2 col3
---------- ---------- ------------
A X n,v,a
A Y t,j(所影响的行数为 2 行)
--*/
insert into tn2 select
'A' ,'X' ,'n'
union select
'A' ,'X' ,'v'
union select
'A' ,'X' ,'a'
union select
'A' ,'Y' ,'t'
union select
'A' ,'Y' ,'j'
构造函数:create function f_try(@a varchar(10))
returns varchar(100)
as
begin
declare @b varchar(100)
set @b=''
select @b=@b+','+col3 from tn2 where col2= @a
set @b=reverse(right(@b,len(@b)-1))
return(@b)
end运行:
select col1,col2,dbo.f_try(col2) from tn2 group by col1,col2结果:
A X n,v,a
A Y t,j
return(stuff(@r,1,1,''))
--------------------------------------------
还是邹老大想得周到。
A X a,v,n
A Y j,t而不是:A X n,v,a
A Y t,j
create function f_try(@a varchar(10),@b varchar(10))
returns varchar(100)
as
begin
declare @c varchar(100)
set @c=''
select @c=@c+','+col3 from tn2 where col2= @b and col1=@a
set @c=reverse(right(@c,len(@c)-1))
return(@c)
end运行:
select col1,col2,dbo.f_try(col1,col2) from tn2 group by col1,col2
to zjcxc(邹建) 你的方法太好了,谢谢,学习