CHAR(13) in SELECT statement

example:
  DECLARE @MSG VARCHAR(4000)
   create table test (TransDt datetime, col1 varchar(1000))
   
   insert into test
   select getdate() TransDt,'first' col1 union
   select getdate()-1,'second' union
   select getdate()-2,'third' union
   select getdate()-3,'fourth'
   
   
  SELECT @MSG = @MSG + CHAR(13)+ CAST(TransDt as varchar(50))+' # '+ col1 FROM test
     SELECT @MSG
     
     
   drop table test

thanks- i set
SET @MSG = ''
now
----------------------------
DECLARE @MSG VARCHAR(4000)
SET @MSG = ''
   create table test (TransDt datetime, col1 varchar(1000))
   
   insert into test
   select getdate() TransDt,'first' col1 union
   select getdate()-1,'second' union
   select getdate()-2,'third' union
   select getdate()-3,'fourth'
   
  SELECT @MSG = @MSG + CHAR(13)+ CAST(TransDt as varchar(50))+' # '+ col1 FROM test
     SELECT @MSG
 
   drop table test
---------------

the above gives me
 Oct 18 2013 11:46AM # fourth Oct 19 2013 11:46AM # third Oct 20 2013 11:46AM # second Oct 21 2013 11:46AM # first

rather i want
    Oct 18 2013 11:46AM # fourth
    Oct 19 2013 11:46AM # third
    Oct 20 2013 11:46AM # second
    Oct 21 2013 11:46AM # first
   
    stored in the variable, which i will then send as the message of an email through sp_send_dbmail. thanks.

 SELECT @MSG = @MSG + CHAR(11) + CHAR(13)+ CAST(TransDt as varchar(50))+' # '+ col1 FROM test

Select allOpen in new window

 Try this.  CHAR(11) is line feed

SQL Server's line feed is CHAR(10) -- use that when you need to advance to the next line.

COANetwork, adding that line gives me:
 Oct 18 2013  1:00PM # fourth Oct 19 2013  1:00PM # third Oct 20 2013  1:00PM # second Oct 21 2013  1:00PM # first

scott,
all of the below give me answer in one line...

   select '1'+CHAR(10)+'2'
   select '1'+CHAR(11)+'2'
   select '1'+CHAR(13)+'2'

just a silly question - are you viewing your results as an Outlook plain-text e-mail?  is that wehre you get your "one line"..?  Remember that Outlook notifies you when it has "removed extra line breaks" from text, and you have to click the yellow warning label to restore them and view original text.  I don't know how Microsoft determines wht is "extra", but it is a pain in the rear.  please verify that your results are not getting butchered by your mail program.

COA, right now I am just talking about SQL Server..

please try
   select '1'+CHAR(10)+'2'
GO
   select '1'+CHAR(11)+'2'
GO
   select '1'+CHAR(13)+'2'

how cna we get the line breaks to show up in these.. we can then take it into the code.