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: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience 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 bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.