Static variable in SQL procedure ?

[pb_b...@yahoo.com]

Hi-

I am trying to write a SQL procedure to do the following:

Aggregate several rows (number is unknown) into a single row based on
an ID field and aggregate the value of another field's values in a
comma-delim list. Example:

ID ------ Color
1 Blue
1 Yellow
1 Red

Result would be a single row with ID = 1 and Color = Blue, Yellow, Red
I have done this in Access by coding a VBA function that is called from
the group by query. It uses a static variable to build the delimited
list. example query: select ID, max(myaggfunction(Color,ID)) as
AggColor from ColorTable group by ID

So how to do this in SQL Server either 2000 or 2005? Any way to do
this in SQL 2000 ? I need to try and avoid using something like temp
tables to store the built up values in. I think it's got to be doable
in SQL 2005 using a CLR routine but I thought I saw somewhere you
cannot use a writable static value in such a CLR routine.

Thanks !

[JeffB]

If you are only dealing with one ID at a time, then the following will
work. If you need to do it to an entire set, then you need to create a
user function that takes in a ID and returns the string.

---------------
declare @Color table (ID int, Color varchar(10))

insert @color values (1, 'Blue')
insert @color values (1, 'Yellow')
insert @color values (1, 'Red')

declare @colorstring varchar(255),
@id int

select @id = 1

select @colorstring = ''

select @colorstring = @colorstring + case when len(@colorstring) > 0
then ', ' else '' end + Color
from @color
where id = @id

select @id, @colorstring

------------------
OR if you need to work with a set try this:
----------------------

drop table Color
go

create table Color(ID int, Color varchar(10))

insert Color values (1, 'Blue')
insert Color values (1, 'Yellow')
insert Color values (1, 'Red')
insert Color values (2, 'Greem')
insert Color values (2, 'Red')

drop function [dbo].[fnConcatColor]
go

create function fnConcatColor
(
@ID as int
)
returns varchar(8000)
as

begin
declare @colorstring varchar(8000)

select @colorstring = ''

select @colorstring = @colorstring + case when len(@colorstring) > 0
then ', ' else '' end + Color
from color
where id = @id

return (@colorstring)
end

GO

select distinct x.id, dbo.fnConcatColor(x.id)
from (select distinct id from color) x

[Aaron Bertrand [SQL Server MVP]]

http://www.aspfaq.com/2529

<pb_b...@yahoo.com> wrote in message
news:1142363263.2...@e56g2000cwe.googlegroups.com...

[Anith Sen]

In general, a good approach is to bring the return the resultset to the
client and massage the data to appropriate display format.

If you really want to get this done in the server, for 2005, try:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/7e5b4c8a9b9b968a

For 2000, try:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e

--
Anith

[pb_b...@yahoo.com]

Thanks to all for your timely replies. This has given me a lot of food
for thought.

PCB