title: How to convert Oracle LISTAGG() function to SQL Server using Stuff()
body: <p>I am trying to convert this Oracle script to T-SQL:</p>
  
  <p>Oracle code:</p>
  
  <pre><code>Select Col1, Col2, Col3, Col4,
         'BDS: ' || LISTAGG(BD, ' , ') WITHIN GROUP (ORDER BY Col5, Col6) || '.' AS BDs
  from(Select Col1, Col2, Col3, Col4, Col5, Col6,
               to_char(Col7) || '-' || Col8 || '-' || to_char(Col5) || '-' || to_char(Col6) BD
       from TBL1
       order by Col6)
  Group BY Col1, Col2, Col3, Col4
  </code></pre>
  
  <p>Sample result:</p>
  
  <pre><code>    COl1 | COl2 | COl3 | Col4 | BDS
      Z1   | 1    | 12   | 1    | BDS: 1-M-12-6-1 + 1-M-12-6-2 + 1-M-12-6-3
      Z1   | 1    | 31   | 1    | BDS: 1-M-31-6-5 + 1-M-31-6-6 + 1-M-31-6-7
  </code></pre>
  
  <p>So far I have tried,</p>
  
  <pre><code>Select 
      Col1, Col2, Col3, Col4,
      'BDS: ' + STUFF (select ' , ' + BD
                       from TBL1
                       group by BD
                       order by Col5, Col6    
                       FOR XML PATH('')), 1, 1, '') + '.' AS BDS    
  from 
      (select 
          Col1, Col2, Col3, Col4, Col5, Col6,
          CONVERT(VARCHAR,Col7) + '-' + Col8+ '-' + CONVERT(VARCHAR,Col5)   + '-' + CONVERT(VARCHAR,Col6) BD
       from TBL1) A1
  Group By 
     Col1, Col2, Col3, Col4, BD
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>     COl1 | COl2 | COl3 | Col4 | BDS
       Z1   | 1    | 12   | 1    | BDS: + 1-M-12-6-1 + 1-M-12-6-1 + 1-M-12-6-1......(repeats for more than 100 times)
       Z1   | 1    | 12   | 1    | BDS: + 1-M-12-6-2 + 1-M-12-6-2 + 1-M-12-6-2......(repeats for more than 100 times)
  </code></pre>
  
  <p>Problem:</p>
  
  <ul>
  <li><code>Col3</code> needs to have unique values</li>
  <li><code>BDS</code> needs to concatenate strings if it's corresponding values in <code>Col3</code> is same</li>
  <li>need to remove the first occurrence of <code>+</code> from <code>BDS</code> column, which I thought the <code>FOR XML PATH('')), 1, 1, ''</code> would do.</li>
  </ul>
  
  <p>Any help would be appreciated.</p>
  
  <p>Thanks.</p>