title: SQL Concatenation using XML Path - multiple rows with multiple table references
body: <p>I am trying to use the XML Path to run SQL Concatenation but I am running into a bit of a problem. I have one table that is being used as a reference table for values I want to concatenate.I have 3 columns in the reference table (M.PROD, S.PROD, &amp; REF NUMB). </p>
  
  <ul>
  <li>M.PROD=====>S.PROD======>Ref Numb </li>
  <li>1===========><strong>_</strong>===========>981024583 </li>
  <li>2===========><strong>_</strong>===========>981024719 </li>
  <li>3===========><strong>A</strong>===========>981024605</li>
  <li>3===========><strong>B</strong>===========>981024669 </li>
  <li>4===========><strong>A</strong>===========>981024688 </li>
  <li>4===========><strong>B</strong>===========>981024706 </li>
  <li>4===========><strong>C</strong>===========>981024723 </li>
  <li>5===========><strong>_</strong>===========>981024742</li>
  <li>6===========><strong>_</strong>===========>981024742</li>
  </ul>
  
  <p>I have the main tables where the m.prod and s.prod are used to match the reference table for Ref Numb values. What I want to do is concatenate the Ref Numb values based on what is being selected in main tables. The out put I am looking for is this: </p>
  
  <ul>
  <li>M.Prod======>Ref Numb</li>
  <li>1===========>981024583</li>
  <li>2===========>981024719</li>
  <li>3===========>981024605, 981024669</li>
  <li>4===========>981024688, 981024706, 981024723</li>
  <li>5===========>981024742</li>
  <li>6===========>981024742</li>
  </ul>
  
  <p>I am using the following query: </p>
  
  <pre><code>SELECT DISTINCT P.PRODUCT, 
      (STUFF((SELECT DISTINCT ',' + P1.REFNUMB AS [text()]
              FROM PRODUCT P1
              WHERE P1.PRODUCT = P.PRODUCT 
              FOR XML PATH('')), 1, 1, ''))
  FROM PRODUCT P
  </code></pre>
  
  <p>This gives me the output of: </p>
  
  <ul>
  <li>M.Prod======>Ref Numb</li>
  <li>1===========>981024583</li>
  <li>2===========>981024719</li>
  <li>3===========>981024605, 981024669</li>
  <li>4===========>981024688, 981024706, 981024723</li>
  <li>5===========>981024742</li>
  <li>6===========>981024742</li>
  </ul>
  
  <p>However, there are times where all the s.prod are not in the main tables. So for this I use this query: </p>
  
  <pre><code>SELECT DISTINCT P.PRODUCT, 
      (STUFF((SELECT DISTINCT ',' + P1.REFNUMB AS [text()]
              FROM PRODUCT P1
              WHERE P1.PRODUCT = P.PRODUCT AND P1.SUBID = P.SUBID
              FOR XML PATH('')), 1, 1, ''))
  FROM PRODUCT P
  </code></pre>
  
  <p>This query produces following output for me: </p>
  
  <ul>
  <li>M.Prod======>Ref Numb</li>
  <li>1===========>NULL</li>
  <li>2===========>NULL</li>
  <li>3===========>981024605</li>
  <li>3===========>981024669</li>
  <li>4===========>981024688</li>
  <li>4===========>981024723</li>
  <li>5===========>NULL</li>
  <li>6===========>NULL</li>
  </ul>
  
  <p>The output I need in these cases is: </p>
  
  <ul>
  <li>M.Prod======>Ref Numb</li>
  <li>1===========>981024583</li>
  <li>2===========>981024719</li>
  <li>3===========>981024605, 981024669</li>
  <li>4===========>981024688, 981024723</li>
  <li>5===========>981024742</li>
  <li>6===========>981024742</li>
  </ul>
  
  <p>Any solution for this will be greatly appreciated, Thank you. </p>