简单问题帮忙解决,马上给分
表1
字段
NO NAME
----------------------
1 AA
1 BB
1 CC
2 aa
2 bb
求一语句得出结果如下
Result
-----------
1 AA,BB,CC
2 aa,bb
[解决办法]
create table tb
(
no int,
name varchar(10)
)
insert into tb(no,name) values(1, 'AA ')
insert into tb(no,name) values(1, 'BB ')
insert into tb(no,name) values(1, 'CC ')
insert into tb(no,name) values(2, 'aa ')
insert into tb(no,name) values(2, 'bb ')
go
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' +name from tb where no= @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct no,dbo.f_hb(no) as name from tb
drop table tb
drop function f_hb
resuno name
----------- ---------
1 AA,BB,CC
2 aa,bb
（所影响的行数为 2 行）
