create table t(n varchar(10),i int) insert t select 'a',1 union select 'b',2 union select 'b',3 union select 'c',4 union select 'c',5 union select 'c',6 select * from t create function m(@i varchar(10)) returns varchar(200) as begin declare @n varchar(2000) set @n='' select @n=@n+''+cast(i as varchar) from t where n=@i return @n end select distinct n,dbo.m(n) as s from t