Add Line Break in Result Set Column Names

Transact-SQL
1

Is there a way to add line breaks to result set column names so when I copy the results with headers to excel, the column names are already have any line breaks that I need them to have.

I know that I can write a query like this: Select EmpVal16 As 'Clock Number' From EmpView to change the output query column name from EmpVal16 to Clock Number. It would be really nice if I could do something like ClockNumber so that it would automatically make the column heading on two lines in Excel. Is this possible? Otherwise, I know I can use ALT-ENTER in Excel to add a line break, but it is a hassle if you have several columns.

2

you can try embedding the line break char(10) + char(13) in your text in dynamic sql, but really this is not something you would typically do in SQL. It's more common to do it in the application layer. e.g. if you run the query from Excel instead of SSMS or use SSRS to generate the Excel or SSIS or roll-your-own .Net.

try this:

declare @sql nvarchar(200) = '

select 1 as [one' + char(10) + char(13) + 'two]'

select @sql
exec sp_executesql @sql

then copy-paste the results to Excel. Do me a favor though and don't put this into production.