show multiple values in single textbox comma separated 1

[codecomm]

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!

 

[xlbo]

I would do this in the SQL statement that populates the dataset in the report...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[codecomm]

xlbo,

Any tips on where / how to do that?

I'll start some Google searches to see what I can find.

Thanks!

 

[codecomm]

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)

 

[codecomm]

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