none
show multiple values in single textbox comma separated

    Question

  •  

    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!

    Wednesday, January 02, 2008 8:50 PM

Answers

  •  

    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

    Tuesday, January 08, 2008 6:16 PM

All replies

  •  

    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)

    Monday, January 07, 2008 8:00 PM
  •  

    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

    Tuesday, January 08, 2008 6:16 PM