title: SQL Server 2008 XML Path - separated list using newline instead of semicolon
body: <p>I have a SSRS report that uses the following query which lists comma separated names:</p>
  
  <pre><code>Select distinct 
      ST2.Country, st2.Location, st2.[Group], 
      substring(
      (
              Select ';'+ST1.[Sponsor]  AS [text()]
              From tbl_de_list ST1
              Where ST1.[W ID] = ST2.[W ID]            
              For XML PATH ('')
       ), 2, 1000) [sponsor]
  From 
      tbl_de_list ST2
  order by 
      st2.[W ID]
  </code></pre>
  
  <p>I need this to be on a new line when exported from ssrs report. </p>
  
  <p>This is what I have : </p>
  
  <pre><code>first name, last name ; first name, last name
  </code></pre>
  
  <p>What I need (in a single column): </p>
  
  <pre><code>first name, last name
  first name, last name
  </code></pre>