create table tt(id int,name varchar(20))
insert into tt
select 1,'aa'
union all select 1,'bb'
union all select 1,'cc'
union all select 2,'dd'
union all select 3,'ee'
union all select 3,'ff'
/*要的结果是
id name
1 aa,bb,cc
2 dd
3 ee,ff现在的问题是不能用自定义函数
直接用SQL语句
*/
insert into tt
select 1,'aa'
union all select 1,'bb'
union all select 1,'cc'
union all select 2,'dd'
union all select 3,'ee'
union all select 3,'ff'
/*要的结果是
id name
1 aa,bb,cc
2 dd
3 ee,ff现在的问题是不能用自定义函数
直接用SQL语句
*/
create table tt(id int,name varchar(20))
insert into tt
select 1,'aa'
union all select 1,'bb'
union all select 1,'cc'
union all select 2,'dd'
union all select 3,'ee'
union all select 3,'ff'go
create function f_str(@id varchar(4))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str = ''
select @str = @str+','+cast(name as varchar(10)) from tt where id=@id
return stuff(@str,1,1,'')
endselect distinct id,dbo.f_str(id) name from tt/*
id,name
--------
1 aa,bb,cc
2 dd
3 ee,ff*/
drop table ttdrop function f_str