create table aaa
(col1 varchar(5),col2 int,col3 varchar(5))

insert into aaa
select 'aaa', 3000, 'zzz' union all
select 'bbb', 3000, 'zzz' union all
select 'ccc', 3000, 'zzz'


select * from aaa

col1  col2        col3
----- ----------- -----
aaa   3000        zzz
bbb   3000        zzz
ccc   3000        zzz


select stuff(
(select ','+col1 from aaa b where b.col2=a.col2 and b.col3=a.col3 
 for xml path('')),1,1,'') col1,
a.col2,a.col3
from aaa a
group by a.col2,a.col3

col1           col2        col3
------------- ----------- -----
aaa,bbb,ccc     3000        zzz

------解决方案--------------------
SQL code

--> 测试数据：[aaa]
if object_id('[aaa]') is not null drop table [aaa]
create table [aaa]([col1] varchar(3),[col2] int,[col3] varchar(3))
insert [aaa]
select 'aaa',3000,'zzz' union all
select 'bbb',3000,'zzz' union all
select 'ccc',3000,'zzz'

go
if OBJECT_ID('pro_test')is not null
drop proc pro_test
go
create proc pro_test
as
declare @col1 varchar(100)
declare @col2 varchar(100)
declare @col3 varchar(100)
set @col1=''
set @col2=''
set @col3=''
select @col1=@col1+ltrim(col1)+',' from(select distinct col1 from aaa)a
select @col2=@col2+ltrim(col2)+',' from(select distinct col2 from aaa)a
select @col3=@col3+ltrim(col3) from(select distinct col3 from aaa)a
select @col1+@col2+@col3 as value

exec pro_test
/*
value
aaa,bbb,ccc,3000,zzz
*/


感觉一条语句不好实现，写了个过程

------解决方案--------------------
自定义函数应该传col2，col3比较适合。
SQL code
-- function
create function getStr(@col2 varchar(50), @col3 varchar(50))
returns varchar(100)
as
begin
    declare @s varchar(100)
    select @s = isnull(@s+',','')+[col1] from aaa where [col2]=@col2 and [col3]=@col3
    return @s
end

-- 执行
select yourstr = dbo.getStr(col2,col3),col2,col3 from aaa 
group by col2,col3