Paste SQL Query output with line breaks into single Excel cell

Question

I'm trying to write SQL code that pulls data from more than one field and displays it in a single field, but on multiple lines. The goal is outputting to Excel retaining multiple lines in a single cell. Our current procedure is just using Excel to concatenate two fields into one cell with carriage returns, but I would like to have SQL do that if possible.

For example:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13)  + 'This is line 2.'
print @text

Displays this:

This is line 1.   
This is line 2.

Changing to a Select:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
select @text

Displays this:

This is line 1. This is line 2.

I want to then copy and paste that output into Excel and have the data appear in a single cell so this is all in one cell:

This is line 1.   
This is line 2.

Answer 1

In your SQL query, instead of carriage returns, use:

   " & CHAR(10) & "

Have the column start with an equals sign and double quote, and end with double quote. So, the output from SQL would be:

="This is line 1." & CHAR(10) & "This is line 2."

Put this into Excel. This will look like a mess, until you make sure that under Format Cells you've checked 'Wrap Text', then the carriage returns will appear within the cells.