SQL Server Performance

show multiple values in textbox comma separated

Discussion in 'SQL Server 2005 Reporting Services' started by bubberz, Jan 2, 2008.

  1. bubberz New Member

    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!
  2. Adriaan New Member

    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
  3. bubberz New Member

    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)
  4. Adriaan New Member

    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, '')
  5. dineshasanka Moderator

    By using Join function you can accomplish that.=Join(Parameters!SupplierID.Value, ", ")
    Hope this helps....
  6. bubberz New Member

    dineshasanka,
    This is for the report parameters right?
  7. bubberz New Member

    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!
  8. Adriaan New Member

    [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.
  9. bubberz New Member

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

    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

Share This Page