We help IT Professionals succeed at work.

Concat into separate lines

D W
D W asked
on
I am using T-SQL and what I am trying to accomplish is concatenate three columns. But I would like each of the columns to start on a new line.  The three columns I have are date,subject,text.
right now this is what I am using:

CONCAT('Date: ', Date, 'Subject: ',Subject, 'Text: ',Text ) As 'Timeline'

How can I modify this concat function to put the date on one line then subject on another line then text on another line?

thank you
Comment
Watch Question

View Solutions Only

Paul MacDonaldDirector, Information Systems
Commented:
"How can I modify this concat function to put the date on one line then subject on another line then text on another line?"
This depends entirely on what you're rendering to.  The shortest answer would be something like
     CONCAT('Date: ', Date + CHAR(13), 'Subject: ',Subject + CHAR(13), 'Text: ',Text ) As 'Timeline'
D W

Author

Commented:
i actually tried that and get this error:
Conversion failed when converting date and/or time from character string.
Director, Information Systems
Commented:
Okay, you'll have to CAST or CONVERT the date value to a char/varchar before concatenating the CHAR(13), I guess.
     CONCAT('Date: ', CAST(Date as VARCHAR) + CHAR(13), 'Subject: ',Subject + CHAR(13), 'Text: ',Text ) As 'Timeline'

That's just a guess.  I don't know what your datatypes are.
D W

Author

Commented:
thank you
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
It seems rather bizarre to me to do your own concatenation within a CONCAT function.  Instead, just put the additional value to be concatenated as its own entry, like this:

CONCAT('Date: ', Date, CHAR(10), 'Subject: ',Subject, CHAR(10), 'Text: ',Text ) As 'Timeline'

The other reason for that is in case the value is NULL; if it is, and you do the concat yourself, the NULL value will wipe out the CHAR(10) (or CHAR(13), whichever).
Paul MacDonaldDirector, Information Systems

Commented:
Thanks for the tip Scott.  What DBA skills I have are self-taught and - while I probably would have come around to that answer in a few years - I appreciate your pointing it out.