New line Character in column alias

[manishkaushik]

Hi Friends,

I have the following query, i am using the column alias by this way,
select work_Code as "Work Code",work_nature as "Work Nature" from sb_cm_work_nature

it works fine and i get this output.

Work Code Work Nature

1 External
2 DE-Sales
3 DE-Miscellaneous
4 DE-Equipments &Transport

But my requirement is to break the Column alias in two line with the new line character as follows

Work Work
Code Nature

1 External
2 DE-Sales
3 DE-Miscellaneous
4 DE-Equipments &Transport

Please guide me how can i proceed.
Thanks you so much.

Manish Kaushik

[Kate Albany]

You would do that kind of thing in the client application displaying the results

[Letscode]

What type of front end application your using?

It really depends on the front end application your using.

[manishkaushik]

Thanks Kate Albany and Letscode for repying.

But i am using the VB.net forms as my front end application and i don't want to do any thing on my front end forms regarding this kind of things, as i have to just process this query on my MS SQL SERVER and the output of the query must have to assigned to the DATAGRID control of the vb.net forms.

Thanks & Regards
Manish Kaushik

[Kate Albany]

Well if you must do it in SQL and not the front-end, this is how I would do it in Oracle:

select 'Work', 'Work'   from dual
union  all
select 'Code', 'Nature' from dual
union  all
select work_code, work_nature
from   sb_cm_work_nature

The table 'dual' in Oracle is a fake table you can use to select manual values from the DB. For instance to get the system date you would do the following:

select sysdate from dual

I know there is an equlivant to Oracle's 'dual' table in MS SQL, but I do not know what it is called.

Though you may have problems if the 'work_code' column is stored as a numerical field, as the columns joined by a 'union all' must be of the same datatype. As you can see the values of 'Work' & 'Code' selected from dual are strings and unless 'work_code' from sb_cm_work_nature is also a string you will have to do something ugly like this:

select 'Work', 'Work'   from dual
union  all
select 'Code', 'Nature' from dual
union  all
select to_char(work_code), work_nature
from   sb_cm_work_nature

Hope that helps,

Kate

[manishkaushik]

Thank u so much kate !
You really did a good job.

it helps me up to 99 %.
But still i m unable to convince my self that :
"Why we can't use the newline character ( viz. \n,\t in C++, C , and vbcrlf in vb,vb.net ) while mentioning the column alias in a select query.

Well Thanks u so much for your effort.

Thanks & Regards
Manish Kaushik

[Estuardo]

G'd evening All!
Manish, in my opinion SQL is not designed to work without a Front End, so, we can't expect sql to have all (same) functions that almost any other OOL have, insted we should take advantage from both ends.... i mean to use the right tool for the job ;)
Just to add a comment to Kate's post. The equivalent for [b]sysdate[b] IF systadate is what the name imply, is in MS SQL GETDATE()
Regards
Estuardo

[manishkaushik]

Thanks Estuardo

Ya i guess your are right.

Thanks to all of your friends.

Thanks & Regards
Manish Kaushik

[szabogi]

Here is some T-SQL code that should acheive your objective:

[code]
-- declare variables
DECLARE @vchColumnOutput varchar(200)
DECLARE @vchWorkCode varchar(100)
DECLARE @vchWorkNature varchar(100)
-- select data from tblWhatever into @variables
SELECT @vchWorkCode = work_code,
@vchWorkNature = work_nature
FROM tblWhatever
-- insert a carriage return between work_code and work_nature
SET @vchColumnOutput = @vchWorkCode +char(13)+ @vchWorkNature
-- display work code and work nature
SELECT @vchColumnOutput
[code]



Hi Friends,

I have the following query, i am using the column alias by this way,
select work_Code as "Work Code",work_nature as "Work Nature" from sb_cm_work_nature

it works fine and i get this output.

Work Code Work Nature

1 External
2 DE-Sales
3 DE-Miscellaneous
4 DE-Equipments &Transport

But my requirement is to break the Column alias in two line with the new line character as follows

Work Work
Code Nature

1 External
2 DE-Sales
3 DE-Miscellaneous
4 DE-Equipments &Transport

Please guide me how can i proceed.
Thanks you so much.

Manish Kaushik