Returning a Comma Delimited string

-- =============================================
-- Comment Block
-- =============================================
-- 
--
-- =============================================
use pubs
go

if exists
(
select name 
from sysobjects 
where name = N'test' 
and type = 'u'
)
    drop table test
go

create table test (
idint,
ordchar( 30 ),
indexnumint
);

/*
205             apples               125
205             shoes                223
305             pizza                 309
305             marbles             789
*/
set nocount on 

insert test values( 205, 'apples', 125 )
insert test values( 205, 'shoes', 223 )
insert test values( 305, 'pizza', 309 )
insert test values( 305, 'marbles', 789 )

set nocount off

-- Values in table
select t2.id, t2.ord, t2.indexnum
           from test t2

-- Grouping by id
select t2.id, min( t2.ord )
from test t2
group by t2.id 
order by t2.id asc

-- Selecting 1 id
select t2.ord
from test t2
where t2.id = 205

/*
This is the usual way to produce a comma delimited list,
but it does not group it. It returns one string for the 
entire results set
*/
declare @OrderList varchar(100)

select 
@OrderList = coalesce( @OrderList + ', ', '') + rtrim( t2.ord )
from test t2
where t2.id = 205

select @OrderList

set @OrderList = null
go

-- Using a scaler function to return the comma delimited list
if 
object_id (N'dbo.concatFieldNames') is not null
drop function dbo.concatFieldNames
go

create function concatFieldNames (@TableID int)
returns varchar(8000)
as
begin
declare @fields varchar(8000)
set @fields = null
select @fields = coalesce( @fields + ', ', '' ) + rtrim( ord )
from test
where id = @tableid
return @fields
end --function
go

-- The select to use the function
select distinct id, dbo.concatFieldNames(ID)
from test

declare @total int

select
@total = coalesce( @total, 0 ) + indexnum
from test

select @total

select sum( indexnum )
from test