SELECT field1,
field2=CAST(MIN(field2) as varchar)
+CASE
WHEN COUNT(*)=3 THEN ','
+CAST((SELECT field2 FROM tb WHERE field1=a.field1 AND field2 NOT IN(MAX(a.field2),MIN(a.field2))) as varchar)
ELSE ''
END
+CASE
WHEN COUNT(*)>=2 THEN ','+CAST(MAX(field2) as varchar)
ELSE ''
END
FROM tb a
GROUP BY field1
create function dbo.fn_Merge(@field varchar(1000))
returns varchar(8000)
as
begin
declare @name varchar(8000)
set @name=''
select @name=@name+'_'+field2 from test where field1=@field
return stuff(@name,1,1,'')
end
go
create table test(field1 int,field2 varchar(10))
insert test select 1,'a'
union all select 1,'b'
union all select 1,'d'
union all select 2,'c'
union all select 2,'a'
select distinct field1,dbo.fn_Merge(field1) from test