Comma

Raw Data:

id name services
-----------------------------------
1 Joe AA
1 Joe AB
1 Joe AC
2 Judy GH
2 Judy GC
3 Kevin AA
3 Kevin GH

Result Set:

id name services
-----------------------------------
1 Joe AA, AB, AC
2 Judy GH, GC
3 Kevin AA, GH








If you have MSSQL2K, then the most performant (and arguably most elegant) way woud be to use a user-defined function:


Your data:

create table t1(id int, name varchar(10), services varchar(10))

insert into t1 values(1,'Joe' ,'AA')
insert into t1 values(1,'Joe' ,'AB')
insert into t1 values(1,'Joe' ,'AC')
insert into t1 values(2,'Judy' ,'GH')
insert into t1 values(2,'Judy' ,'GC')
insert into t1 values(3,'Kevin','AA')
insert into t1 values(3,'Kevin','GH')

.. the function:

create function my_comma_sep ( @id int) returns varchar(4000) as
begin
declare @result varchar(4000)
set @result = ''
select @result = @result
+ case when len(@result)>0 then ',' else '' end
+ services
from t1
where id = @id
return @result
end



Now you can use the function like this:

select id,name, dbo.my_comma_sep(id)
from (select distinct id,name from t1) x

..or like this:

select id, max(name), dbo.my_comma_sep(id)
from t1 group by id

... or like this:

select id, name, dbo.my_comma_sep(id)
from t1 group by id,name

... and the result:

1 Joe AA,AB,AC
2 Judy GH,GC
3 Kevin AA,GH

Comma Seprated Input

=======================
CREATE PROCEDURE dbo.sp1
@list as varchar(200)
AS

exec ( 'SELECT field1, field2, field3 FROM Table1 WHERE UPPER(RIGHT(RTRIM(field1),3)) IN ( ' + @list + ' ) ' )
=========================



and you need to call this proc as below

exec sp1 @list = '''DFG'',''ABC'',''ASD'',''FGH'''