SQL SERVER FOR XML PATH - Exporting data to excel using Integration Services

Question

Using the help of SO and code below:

select t1.*,
       stuff( (select '; ' + coalesce(data1, '') + ',' + coalesce(data2, '')
               from table2 t2
               where t2.FK_TBL1_ID = t1.id
               for xml path ('')
              ), 1, 2, ''
            ) as Data1Data2
from table1 t1;

I successfully combined multiple rows with multiple columns into one row-one column in my sql view.

What i would ultimately like to achieve is to be able for each row with multiple columns combined, to break the line for the new record(row) when viewed inside an excel cell like below:

**Data1Data2Cell**

  aaaa, bbbb; 
  cccc, dddd;
    ....

Same functionality can be achieved in Excel using ALT+ENTER on each cell.

I tried using Char(10) and Char(13) to no avail.

Answer 1

You should change your first line of STUFF to this

 stuff( (select CHAR(10)+ coalesce(data1, '') + ',' + coalesce(data2, '')+'; '

This will introduce the necessary line break in the string correctly.

See working demo here

Please note that when you run this query in SSMS, you may not see the results correctly as typically results show up in grid view like below

To get the right view you should change your query results to text view by either pressing cntrl+ t or by clicking Query options from top menu(see screenshot below)

Finally after this your query will give you correct view of results like below