title: SQL Server trigger to concatenate strings and append additional text
body: <p>I have 2 tables in MS SQL Server 2008:</p>
  
  <p>Table: ADOMAccounts</p>
  
  <pre><code>AccountID   ADOMName     ADOMPermission
  1000        Company_A    Read_Only
  1000        Company_A    Read_Write
  1001        Company_B    Read_Only
  1001        Company_B    Read_Write
  </code></pre>
  
  <p>Table: SUBSCRIBERS  (ADOMPermission is inserted separately per user)</p>
  
  <pre><code>USERNAME    REPLYATTR           AccountID    ADOMPermission
  alice       NULL                1000         Read_Only
  bob         NULL                1001         Read_Write
  </code></pre>
  
  <p>We currently have a trigger on inserts to SUBSCRIBERS to update the REPLYATTR value for 'bob' with:</p>
  
  <pre><code>Name="Company_B",Profile="Read_Write",Service-Type="Login-User"
  </code></pre>
  
  <p>Trigger: tr_GenerateReplyAttr</p>
  
  <pre><code>ALTER TRIGGER tr_GenerateReplyAttr ON SUBSCRIBERS
  FOR INSERT, UPDATE
  AS
  UPDATE Existing
  SET Replyattr = 'Name="' + Accounts.ADOMName +
                      '",Profile="' + New.ADOMPermission +
                      '",Service-Type="Login-User"'
  
  FROM Subscribers AS Existing
      INNER JOIN inserted AS New ON New.Username = Existing.Username
      INNER JOIN ADOMAccounts AS Accounts 
          ON New.AccountID = Accounts.AccountID AND New.ADOMPermission = Accounts.ADOMPermission
  WHERE New.AccountID != '' AND New.ADOMPermission != ''
  
  RETURN
  </code></pre>
  
  <p>But we now have a need that if ADOMAccounts contained Company_C with the same AccountID as Company_B, that the REPLYATTR for 'bob' would look like this:</p>
  
  <pre><code>Name="Company_B",Name="Company_C",Profile="Read_Only",Service-Type="Login-User"
  </code></pre>
  
  <p>So basically matching on AccountID and concatenating distinct ADOMName's with prepended text, appended by text and values.  There shouldn't be too many ADOMName's on the same AccountID (2-3 maybe), but could possibly be more.</p>
  
  <p>I've been looking through other articles on concatenating strings, but fell down on making them distinct and appending the additional text and values.  </p>