select eid+' ' +ename from emp
The above query prints like
----
1 xxx
Instead of printing in the same line how to split the data into two lines like
1
xxx
if any help is highly appreciatable
asked
splitting columns into two lines by using select statement
I'm sorry but: 1 xxx and 1 xxx look the same. Could you further clarify what it is that you're trying to do?
I'm curious why you're trying to do this, Mohan. Surely the job of the query is to get the data, and it's the application's job to display it.
Or use CHAR(13) - carriage return
select eid + char(13) + ename
To 'see' the result, you must be running results to text (Ctrl-T)
select '1'+' ' +'kev'
select '1'+char(10)+'kev'
select '1'+char(13)+'kev'
gives
-----
1 kev
(1 row(s) affected)
-----
1
kev
(1 row(s) affected)
-----
1
kev
(1 row(s) affected)
As Grant mentioned both results look the same. I'm making the assumption that you want the 1 on the first line, and the xxx on the second. Rather than putting a space between the two columns, you need to put a line feed
SELECT eid + CHAR(10) + ename
On windows to be 100% correct it should be:
SELECT Column + CHAR(13) + CHAR(10) + Column2
As on Windows platform the correct new line is represented by CR+LF (Carriage Return + Line Feed)
Its not a question of splitting , its a question of NOT joining ... (Assumming both columns are compatible datatypes)
select eid from emp
union
Select ename from emp
I would strongly suggest you simply no do this. TSQL is a pretty crummy language for formatting. It's designed to retrieve or manipulate data within tables. It's not well constructed for making the data pretty. If anything, I'd suggest that Dave Ballantyne's answer is the best for your needs.
In general I agree with you. Formatting should be the application or display layers job. There are a couple of times when this can be useful though, such as if the output is meant to be written directly into a file, put into an automated e-mail, or used to generate dynamic SQL. Of course, those tend to be the exceptions, as a general rule I would not do this in SQL.
This sounds like something that really needs to happen at the application level. It would be very easy to read the data as two fields and format it in the output where you have more control. The problem is made worse if your target audience is using multiple computer types at which point it is almost manditory to do this at the application level.
To view the true output you must look at it in native text mode. It should be further emphasized that the display in the grid will remove any of the control characters and it will require that the output must be viewed in text mode. My preference is to export it to a text file
