Post reply

Concatenating a field from mulitple rows into one row

  • June 28, 2007 at 10:03 am

    #124516

    Hi

    We are trying to build a datawarehouse. We have a person table which has refers to a conditions table. Basically each person may have multiple conditions. However each conidition is just a very short string and in reality only one or two conditions exist for each person. We would like to concatenate the conditions for each person into a single field in the person table. So we need to return all conditions for each person, get the condition field from each record and concatenate them then insert into the person table.

    Is there any way of achieving this without using cursors (my cursor driven trial does 1000 rows in ten mins and we are talking about 400000 rows)

    Thanks in advance

  • June 28, 2007 at 10:10 am

    #715912

    Can you post a table structure and sample data?

    Is there a maximum number of conditions one person could have?

  • June 28, 2007 at 10:54 am

    #715928

    Alan,

     

    One way to handle it is with a UDF - thanks to whoever posted this -

    CREATE FUNCTION fn_ConcatStrings

    (@ID int)

    RETURNS varchar(500)

    AS

    BEGIN

    declare @String varchar(500)

    select @String = coalesce(@String,'') + Col + ','

    from TblConditions

    where [Id] = @ID

    if Right(@String,1) = ','

    set @String = substring(@String,1,len(@String) -1)

    return @String

    END

    GO

    -- =============================================

    -- Example to execute function

    -- =============================================

    SELECT [id], dbo.fn_ConcatStrings ([id])

    from TblPersons

    Good luck,

    Harley

     

  • June 29, 2007 at 1:56 am

    #716087

    Thank you, that is a smart bit of code Harley. I've never seen coalesce used in that way before, not sure I understand completely why it works! Will do some background reading, however it does just what I want it to. Thanks again

    Alan

  • June 29, 2007 at 2:37 am

    #716097

    Got it now. The coalesce is just to prevent trying to concatenate with a null (so would also work using Isnull). Basically the output from the select goes into @string + @string + new row. Simple and elegant!

    Thanks again

  • June 29, 2007 at 3:18 am

    #716106

    It can be even nicer, without any IFs - COALESCE function takes care of the delimiter and will not place it at the end of the string:

    CREATE FUNCTION fn_ConcatStrings (@ID int)

    RETURNS varchar(500)

    AS

    BEGIN

    declare @String varchar(500)

    select @String = coalesce(@String +', ','') + Col

    from TblConditions

    where [Id] = @ID

    return @String

    END

  • July 2, 2007 at 11:15 pm

    #716734

    Spot on, Vladan...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply