给你个例子:create table t
(id int,test varchar(50),Rank int)insert t
select 1,'Hello',1 union all
select 1,'Mr',2 union all
select 1,'King',3 union all
select 2,'I',1 union all
select 2,'am',2 union all
select 2,'Best',4 union all
select 2,'The',3
go
create function f_union(@id int)
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+' '+test from t where id=@id order by Rank
return(stuff(@sql,1,1,''))
end
goselect id,dbo.f_union(id) as test from t group by id
(id int,test varchar(50),Rank int)insert t
select 1,'Hello',1 union all
select 1,'Mr',2 union all
select 1,'King',3 union all
select 2,'I',1 union all
select 2,'am',2 union all
select 2,'Best',4 union all
select 2,'The',3
go
create function f_union(@id int)
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+' '+test from t where id=@id order by Rank
return(stuff(@sql,1,1,''))
end
goselect id,dbo.f_union(id) as test from t group by id
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(col2 as varchar(100)) from tb where col1=@col1
return stuff(@sql,1,1,'')
endcreate table tb (col1 varchar(100),col2 varchar(100))insert into tb select '计算机021','张三' union all
select '计算机021','李四' union all
select '计算机021','王屋' union all
select '计算机021','郭达' union all
select '电子商务032','小梅' union all
select '电子商务032','陈镇' union all
select '电子商务032','德华' union all
select '电子商务032','佩斯' union all
select '电子商务032','本山'
select col1,dbo.fc_str(col1) as col2 from tb group by col1/*结果:
col1 col2
电子商务03 小梅,陈镇,德华,佩斯,本山
计算机021 张三,李四,王屋,郭达*/