title: How to combine two rows with comma separator in sql server 2008
body: <p>I tried as shown below</p>
  
  <pre><code>SELECT SUBSTRING(
         (
             SELECT td.Text + ', ' AS 'data()',
                    Tda.FirmID
             FROM   tblData1 tda
                    INNER JOIN tblData2 Td
                         ON  Tda.ID = Td.ID
             GROUP BY
                    Tda.Enable1,
                    Tda.ID,
                    Td.Text
             HAVING ISNULL(Enable1, 0) = 1
                    FOR XML PATH('')
         ),
         1,
         10000
     ) AS Csv
  </code></pre>
  
  <p><b>Output</b>:</p>
  
  <pre><code>Landlord Tenant, &lt;FirmID&gt;1&lt;/FirmID&gt;NJ Traffic, &lt;FirmID&gt;1&lt;/FirmID&gt;
  </code></pre>
  
  <p><b>Expected Output</b>: Should be in table format</p>
  
  <pre><code>       csv                          FirmID
  Landlord Tenant, NJ Traffic           1
  </code></pre>