Include a carriage return in a column heading

: Joel Lipman; SQL Server; 07 July 2014; Hits: 9984

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:

copy raw

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
...

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:

copy raw

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
...

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:

copy raw

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

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:

copy raw

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

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)