title: Concatenate Rows into String with Many to Many Joins
body: <p>I have a query that is working for almost all my data scenarios...except one.  I have a master table, a detail table (with one to many line items), and many description tables that join to the detail table (Code Descriptions, etc).  I need to concatenate all detail records into one string value so that I have one record per master record.  So if there's 3 details for the master record, all values need to be concatenated into one record to represent the master.</p>
  
  <p>My issue comes when the Master record has multiple detail records but the detail records don't have a match to the description tables 1 to 1.  For instance, in my scenario the master record has 3 detail records which only one of the detail records has a description record.  So 1 to 3 to 1, Master to Detail to Description.  This is causing an issue.  When trying to concatenate the records the code is not working because of the NULL value created from the Detail to Description join.  The only way I can seem to get this to work is to do a Distinct sub-query and then do my concatenation logic on the outside of that.  I feel like there has to be a better way or that I am simply missing something.  I provided example code below to show my issue.  There are 3 selects that get run.  The first is the flat result with all records from the joins.  The second is my original logic showing the flaw.  The third is a working version that I am hoping someone knows how to do better.  I greatly appreciate any help with this issue.</p>
  
  <pre><code>DECLARE @Notification table(
      SystemID int NOT NULL,
      NotificationID int
      );
  
  DECLARE @NotificationItems table(
      SystemID int NOT NULL,
      NotificationID VARCHAR(100),
      LineItem VARCHAR(100)
      );    
  
  DECLARE @NotificationCauses table(
      SystemID int NOT NULL,
      NotificationID VARCHAR(100),
      LineItem VARCHAR(100),
      TestValue VARCHAR(100)
      );    
  
  INSERT INTO @Notification 
  SELECT 40,1 UNION 
  SELECT 40,2 UNION 
  SELECT 40,3 UNION 
  SELECT 40,4
  INSERT INTO @NotificationItems 
  SELECT 40,1,1 UNION 
  SELECT 40,1,2 UNION 
  SELECT 40,1,3 UNION 
  SELECT 40,2,1 UNION 
  SELECT 40,2,2 UNION 
  SELECT 40,3,1
  INSERT INTO @NotificationCauses 
  SELECT 40,1,1,'Code_A' UNION 
  SELECT 40,2,1,'Code_B' UNION 
  SELECT 40,2,2,'Code_C' UNION 
  SELECT 40,3,1,'Code_D'
  
  --SELECT  *
  --FROM    @Notification
  --SELECT  *
  --FROM    @NotificationItems
  
  SELECT *
  FROM    @Notification AS n
  LEFT OUTER JOIN @NotificationItems AS ni
      ON n.NotificationID = ni.NotificationID
      AND n.SystemID = ni.SystemID
  LEFT OUTER JOIN @NotificationCauses AS nc
      ON ni.NotificationID = nc.NotificationID
      AND ni.SystemID = nc.SystemID
      AND ni.LineItem = nc.LineItem
  
  
  SELECT DISTINCT n.SystemID, n.NotificationID
  ,SUBSTRING(
      (
          SELECT DISTINCT
           CASE WHEN LTRIM(RTRIM(ni1.LineItem)) &lt;&gt; ISNULL('','') THEN ', '+ni1.LineItem ELSE '' END AS [text()] 
          FROM @NotificationItems AS ni1
          WHERE ni1.SystemID = ni.SystemID AND ni1.NotificationID = ni.NotificationID --AND a1.LineItem = a.LineItem
          ORDER BY 1
          FOR XML PATH ('')
      ), 2, 1000) AS [LineItem]
  ,SUBSTRING(
      (
          SELECT DISTINCT
           CASE WHEN LTRIM(RTRIM(nc1.TestValue)) &lt;&gt; ISNULL('','') THEN ', '+nc1.TestValue ELSE '' END AS [text()] 
          FROM @NotificationCauses AS nc1
          WHERE nc1.SystemID = nc.SystemID AND nc1.NotificationID = nc.NotificationID --AND nc1.LineItem = nc.LineItem
          ORDER BY 1
          FOR XML PATH ('')
      ), 2, 1000) AS [TestValues]
  FROM    @Notification AS n
  LEFT OUTER JOIN @NotificationItems AS ni
      ON n.SystemID = ni.SystemID
      AND n.NotificationID = ni.NotificationID
  LEFT OUTER JOIN @NotificationCauses AS nc
      ON ni.SystemID = nc.SystemID
      AND ni.NotificationID = nc.NotificationID
      AND ni.LineItem = nc.LineItem
  
  
  SELECT DISTINCT SystemID, NotificationID
  ,SUBSTRING(
      (
          SELECT DISTINCT
           CASE WHEN LTRIM(RTRIM(a1.LineItem)) &lt;&gt; ISNULL('','') THEN ', '+a1.LineItem ELSE '' END AS [text()] 
          FROM @NotificationItems AS a1
          WHERE a1.SystemID = a.SystemID AND a1.NotificationID = a.NotificationID --AND a1.LineItem = a.LineItem
          ORDER BY 1
          FOR XML PATH ('')
      ), 2, 1000) AS [LineItem]
  ,SUBSTRING(
      (
          SELECT DISTINCT
           CASE WHEN LTRIM(RTRIM(a1.TestValue)) &lt;&gt; ISNULL('','') THEN ', '+a1.TestValue ELSE '' END AS [text()] 
          FROM @NotificationCauses AS a1
          WHERE a1.SystemID = a.SystemID AND a1.NotificationID = a.NotificationID --AND a1.LineItem = a.LineItem
          ORDER BY 1
          FOR XML PATH ('')
      ), 2, 1000) AS [TestValues]    
  FROM    
  (    
  SELECT DISTINCT n.NotificationID, n.SystemID, ni.LineItem, nc.TestValue
  FROM    @Notification AS n
  LEFT OUTER JOIN @NotificationItems AS ni
      ON n.SystemID = ni.SystemID
      AND n.NotificationID = ni.NotificationID
  LEFT OUTER JOIN @NotificationCauses AS nc
      ON ni.SystemID = nc.SystemID
      AND ni.NotificationID = nc.NotificationID
      AND ni.LineItem = nc.LineItem
  ) AS a
  </code></pre>