Include a carriage return in a column heading

Applies To:
  • MS SQL Server 2008 R2
  • MS Windows 7 Enterprise (Client)
  • MS Excel 2010

What?
A really quick note on how to insert a carriage return or new line into the column name/alias (the header). It might seem trivial but these little aesthetic changes done at the database level can save some time.

Why?
I have an Excel report which dynamically gets its content from a data source located on a database on the other side of the world. I want the header in the column "Academic Week" to break across two lines so that the column doesn't expand to the width of "Academic Week" and instead expands to the width of the word "Academic".

What I have:
Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday
---------------- ----------- ----------- ----------- ----------- -----------
1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2                21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...
What I want:
Academic 
Week      Monday      Tuesday     Wednesday   Thursday    Friday
--------- ----------- ----------- ----------- ----------- -----------
1         14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2         21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...


How?
To do this in a select query resultset, you insert the special character references "CHAR(10)" [line feed] and "CHAR(13)" [carriage return] but to do this in the name of the column heading, the answer is a much simpler one, in your SQL statement, simply place your cursor where you want the carriage return and press Return/Enter. This has to be a label to the name of the column enclosed between square brackets or double-quotes:

Before:
SELECT			
	calendar.WeekNumber AS [Academic Week],		
	calendar.Monday,
	calendar.Tuesday,
	calendar.Wednesday,
	calendar.Thursday,
	calendar.Friday
FROM			
	calendar

-- yields as above:

-- Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday
-- ---------------- ----------- ----------- ----------- ----------- -----------
-- 1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
After:
SELECT			
	calendar.WeekNumber AS [Academic 
	Week],		
	calendar.Monday,
	calendar.Tuesday,
	calendar.Wednesday,
	calendar.Thursday,
	calendar.Friday
FROM			
	calendar

-- yields:

-- Academic 
-- Week             Monday      Tuesday     Wednesday   Thursday    Friday
-- ---------------- ----------- ----------- ----------- ----------- -----------
-- 1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014

Last Step:
Format the cells in Excel containing the header under "Alignment" to "Wrap Text". The difference the above does, is that when you refresh the data, it will retain the carriage return in the column headings.

Additional:
  • Only appears obvious in SQL Server Management Studio resultset.
  • Remember to format the cells (column headings) in the Excel spreadsheet for it to wrap text and save the file. Hereafter, you will NOT need to do this on each data refresh.
  • Ensure that there is a trailing space after the label and before the carriage return otherwise the column will return as one word (ie. there has to be a space in between the two words "Academic" and "Week" otherwise the system will only understand the carriage return and often return it as "AcademicWeek").


Other Search(es)
  • Format a column alias with new line
  • Using carriage return in column names
  • New line Character in column alias


Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.