Concatenate Multiple Records Into One Field

Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

TABLE
ColumnA ColumnB
1......A
2......B
3......C
4......A
5......A
6......B
7......C
8......D
9......C
10.....E

EXPECTED OUTPUT
ColumnA ColumnB
1......4,5
2......6
3......7
4......1,5
5......1, 4
6......2
7......3
8......
9......3,7
10.....create function ConcatFld (@.RowId int, @.RowVal char(1))
returns varchar(100) AS
begin
declare @.Ret varchar(100)
set @.Ret=''
select @.Ret= @.Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@.RowVal and ColA<>@.RowId
if len(@.Ret) > 0
set @.Ret = left(@.Ret,len(@.Ret)-1)
return @.Ret
end
--------

select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1|||Thanks Upalsen, this is exactly what I need!!!

concatenate multiple fields

I don't have any troubles concatenating two fields together but so far, I
haven't been able to concatenate more than two fields.
(Order.FirstName+' '+order.LastName) as Name
will work
(Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
will not work.
Any suggestions?I can't think of any reason that shouldn't work.
What is the exact error?
Is this a problem you see in Query Analyzer or
via your "client" code such as ASP or ASP.NET?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"jeff fisher" <jeff@.fisher.com> wrote in message
news:eWY6$uvlFHA.3120@.TK2MSFTNGP09.phx.gbl...
>I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?|||If by "not work" you mean the seconds example is NULL, then it's because the
middle name field is NULL -- concatenating any char field with NULL yeilds
NULL; mathmatical, bitwise, and other operations have similar behavior.
Anyways this is a display issue and would be better handled by your client
code, but for some solutions, in BOL look up:
- ISNULL
- COALESCE
- SET CONCAT NULL YIELDS NULL
"jeff fisher" wrote:

> I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
What the heck does "will not work" mean' Would you like to go to a
doctor that tells you something like that? Why did you use a reserved
word for a table name'
My guess -- based on absolutely nothing you told us -- is that you
have NULL-able columns and do not know that NULLs propagate, one of the
most basic priniciples in SQL.