April 13, 2016 at 4:23 am
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
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
April 13, 2016 at 4:58 am
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