show multiple values in textbox comma separated

I have a field called "Owners", and it's a child to an "Activities" table.
An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.
I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.
I'm kinda stuck on how to do this.
Thanks!

You can create a user-defined function along these lines -
CREATE FUNCTION dbo.MyFunction (@ParentId INT)
RETURNS VARCHAR(MAX)
AS
DECLARE @t VARCHAR(MAX)
SET @t = ''
SELECT @t = @t + CASE WHEN LEN(@t) = 0 THEN '' ELSE ',' END + x.Code
FROM dbo.ChildTable x WHERE x.ParentId = @ParentId
ORDER BY x.Code

RETURN (@t)

END

I'm trying to use a UDF (we're on SQL Server 2005), and not getting it 100%.
The structure is Activities to Owners to SAN.
Activities can have 1 to many Owners, and the owners has a number (kina like SSN) linking to SAN which has their name.
USE DB
GO
CREATE FUNCTION dbo.ConcatOwners4(@ActID CHAR(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN ''
THEN SAN.Authority
ELSE @Output + ', ' + SAN.Authority
END
FROM Activity Left Outer JOIN
Owner ON Activity.ActivityID = Owner.ActivityID Left outer JOIN
SAN ON Owner.OwnerZNumber = SAN.Authority
WHERE Activity.ActivityID = @ActID
ORDER BY Authority
RETURN @Output
END
GO
****************
...and run this:
SELECT DISTINCT ActivityID, ActivityDesc, dbo.ConcatOwners4(ActivityID) AS Expr1
FROM Activity

...I get nothing for the Owners names (Authority)

The concatenation is not happening because you're messing up the assignment syntax:

SELECT
@Output
= @Output
+ CASE WHEN LEN(@Output) = 0 THEN '' ELSE ',' END
+ SAN.Authority
Also check if SAN.Authority can be null - in that case, make the last bit:

+ ISNULL(SAN.Authority, '')

dineshasanka,
This is for the report parameters right?

SELECT@Output=
@Output+
CASE WHEN LEN(@Output) = 0 THEN '' ELSE ',' END+
SAN.Authority
...still gives me NULL in the last column, when I know there's data.
Thanks for the suggestion!

[quote user="bubberz"] SELECT@Output=
@Output+
CASE WHEN LEN(@Output) = 0 THEN '' ELSE ',' END+
SAN.Authority
...still gives me NULL in the last column, when I know there's data.
Thanks for the suggestion!
[/quote]Well, that's why I suggested to use + ISNULL(SAN.Authority) instead of + SAN.Authority.

...tried isNull(), and just gives me blank.
Almost 100% of the activities have one or more owners.

This did the trick:
CREATE FUNCTION dbo.ConcatOwners4(@ActID CHAR(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = SUBSTRING(
(SELECT ',' + SAN.Authority AS "text()"
FROM Activity
Left Outer JOIN Owner ON Activity.ActivityID = Owner.ActivityID
Left outer JOIN SAN ON Owner.OwnerZNumber = SAN.Authority
WHERE Activity.ActivityID = @ActID
ORDER BY Authority
FOR XML PATH('')) ,2,8000)
RETURN @Output
END