请高手指点--SQL语句问题有表T如下:
id name
--------------
1 a
1 b
1 c
2 x
2 y
3 z如何显示成下表样子1 a,b,c
2 x,y
3 z *********************************
http://w.leadbbs.com/a/a.asp?B=205&ID=2271437http://www.hur.cn/bbs/dispbbs.asp?boardID=120&ID=50539&page=1
id name
--------------
1 a
1 b
1 c
2 x
2 y
3 z如何显示成下表样子1 a,b,c
2 x,y
3 z *********************************
http://w.leadbbs.com/a/a.asp?B=205&ID=2271437http://www.hur.cn/bbs/dispbbs.asp?boardID=120&ID=50539&page=1
insert into tb
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'x' union all
select 2,'y' union all
select 3,'z'
gocreate function dbo.fc_str(@id varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(name as varchar(100)) from tb where id=@id
return stuff(@sql,1,1,'')
end
goselect id,dbo.fc_str(id) as name from tb group by id
create function f_str(@id int)
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select @re=@re+','+name from tb where id=@id
return stuff(@re,1,1,'')
end
go
select id,max(dbo.f_str(id)) from tb group id