Returning a Comma Delimited string

,

One of the small quirks of SQL is that it can add each row to a variable.

In the most common case, this is used to generate a comma delimited list.

The script shows how to do this wiht a scalar function, so that multiple rows are returned.

The script shows calculating the column sum the hard way and compares the result to the standard aggregate function SUM().

Of interest is that apart from the output, both sum and incrementing the variable had the same execution plan cost.

-- =============================================
-- 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

Rate

Share

Share

Rate