create table tab1 ( A varchar(10), B varchar(10) ) insert into tab1(A,B) values('a','1') insert into tab1(A,B) values('a','2') insert into tab1(A,B) values('a','3') insert into tab1(A,B) values('b','2') insert into tab1(A,B) values('b','5') insert into tab1(A,B) values('c','2') insert into tab1(A,B) values('c','3') insert into tab1(A,B) values('c','4')
go
create function f_hb(@a VARCHAR(10)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' +B from tab1 where A = @a set @str = right(@str , len(@str) - 1) return(@str) End go
select distinct A,dbo.f_hb(A) as name from tab1 drop table tab1 drop function f_hb
create table tab1 ( A varchar(10), B varchar(10) ) insert into tab1(A,B) values('a','1') insert into tab1(A,B) values('a','2') insert into tab1(A,B) values('a','3') insert into tab1(A,B) values('b','2') insert into tab1(A,B) values('b','5') insert into tab1(A,B) values('c','2') insert into tab1(A,B) values('c','3') insert into tab1(A,B) values('c','4')
go
create function f_hb(@a VARCHAR(10)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + '、' +B from tab1 where A = @a set @str = right(@str , len(@str) - 1) return(@str) End go
select distinct A,dbo.f_hb(A) as B from tab1 drop table tab1 drop function f_hb
(
A varchar(10),
B varchar(10)
)
insert into tab1(A,B) values('a','1')
insert into tab1(A,B) values('a','2')
insert into tab1(A,B) values('a','3')
insert into tab1(A,B) values('b','2')
insert into tab1(A,B) values('b','5')
insert into tab1(A,B) values('c','2')
insert into tab1(A,B) values('c','3')
insert into tab1(A,B) values('c','4')
go
create function f_hb(@a VARCHAR(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' +B from tab1 where A = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct A,dbo.f_hb(A) as name from tab1
drop table tab1
drop function f_hb
(
A varchar(10),
B varchar(10)
)
insert into tab1(A,B) values('a','1')
insert into tab1(A,B) values('a','2')
insert into tab1(A,B) values('a','3')
insert into tab1(A,B) values('b','2')
insert into tab1(A,B) values('b','5')
insert into tab1(A,B) values('c','2')
insert into tab1(A,B) values('c','3')
insert into tab1(A,B) values('c','4')
go
create function f_hb(@a VARCHAR(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '、' +B from tab1 where A = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct A,dbo.f_hb(A) as B from tab1
drop table tab1
drop function f_hb