Post reply

Help with an Update

  • April 13, 2016 at 4:23 am

    #309908

    Hi there,

    I have the following UPDATE statement, which is nearly how I want it to work, but isn't quite there.

    update cgd

    set cgd.FormattedResponsibleNames =

    ltrim(STUFF((ISNULL(', '+ltrim(rtrim(l1.Description)),' ') +

    CASE WHEN ltrim(rtrim(cd.Forename))='SETTONULL' THEN ''

    ELSE ' '+ltrim(rtrim(cd.Forename))

    END +

    ISNULL(' '+ltrim(rtrim(cd.Surname)),'')

    ) ,1,1,''))

    FROM Table1 cgm

    LEFT JOIN Table2 cgd ON cgd.ContactGroupId = cgm.ContactGroupId AND cgd.EffectiveToDate IS NULL

    LEFT JOIN Table3 cd ON cd.ContactId = cgm.ContactId AND cd.EffectiveToDate IS NULL

    LEFT JOIN Table4 l1 ON l1.LookupReference = cd.TitleId AND l1.LookupTypeId = 48

    WHERE cgm.Responsible = 1

    and cgm.ContactGroupId = 5612

    If I take anything after the UPDATE AND SET and place it in a SELECT I actually get two Results as two rows in the cgm (Table 1) are marked as Responsible and are part of a specific ContactGroup.

    The above UPDATE only updates cgd.FormattedResponsibleNames with 1 of the results. How would I get it to update cgd.FormattedResponsibleNames with both the results so some thing like below -

    RESULTS OF SELECT STATEMENT

    Mrs Responsible and Mr Responsible

    WHAT MY SCRIPT UPDATES cgd.FormattedResponsibleNames TO

    Mrs Responsible

    WHAT I WOULD LIKE MY SCRIPT TO UPDATE cgd.FormattedResponsibleNames TO

    Mrs Responsible, Mr Responsible.

    Thanks

  • April 13, 2016 at 4:35 am

    #1871630

    Table definitions (as CREATE TABLE statements) and sample data (as INSERT statements) please

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 13, 2016 at 4:58 am

    #1871634

    Hi,

    You can try using using FOR XML PATH() , an example is here :

    -- > https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

    Cheers,

    Robert

Viewing 3 posts - 1 through 2 (of 2 total)

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