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:
copyraw
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
...
  1.  Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday 
  2.  ---------------- ----------- ----------- ----------- ----------- ----------- 
  3.  1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014 
  4.  2                21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014 
  5.  ... 
What I want:
copyraw
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
...
  1.  Academic 
  2.  Week      Monday      Tuesday     Wednesday   Thursday    Friday 
  3.  --------- ----------- ----------- ----------- ----------- ----------- 
  4.  1         14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014 
  5.  2         21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014 
  6.  ... 


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:
copyraw
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
  1.  SELECT 
  2.      calendar.WeekNumber AS [Academic Week], 
  3.      calendar.Monday, 
  4.      calendar.Tuesday, 
  5.      calendar.Wednesday, 
  6.      calendar.Thursday, 
  7.      calendar.Friday 
  8.  FROM 
  9.      calendar 
  10.   
  11.  -- yields as above: 
  12.   
  13.  -- Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday 
  14.  -- ---------------- ----------- ----------- ----------- ----------- ----------- 
  15.  -- 1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014 
After:
copyraw
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
  1.  SELECT 
  2.      calendar.WeekNumber AS [Academic 
  3.      Week], 
  4.      calendar.Monday, 
  5.      calendar.Tuesday, 
  6.      calendar.Wednesday, 
  7.      calendar.Thursday, 
  8.      calendar.Friday 
  9.  FROM 
  10.      calendar 
  11.   
  12.  -- yields: 
  13.   
  14.  -- Academic 
  15.  -- Week             Monday      Tuesday     Wednesday   Thursday    Friday 
  16.  -- ---------------- ----------- ----------- ----------- ----------- ----------- 
  17.  -- 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

Category: SQL Server :: Article: 568

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

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.