January 2, 2008 at 1:52 pm
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!
January 8, 2008 at 11:17 am
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
January 9, 2008 at 6:34 am
I've often wondered how to do this in SQL - that's a neat solution.
January 10, 2008 at 5:40 am
You can also use the COALESCE() function to pull everything together.
Declare @cols varchar(4000);
Select @cols = COALESCE(@cols + ', ' + MyColName, MyColName)
from MyTable Order by MyDate;
Select @cols;
The order by clause isn't required. I just copied this over from something I had.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply