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

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

What type of front end application your using?

It really depends on the front end application your using.

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

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

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

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 sysdate IF systadate is what the name imply, is in MS SQL GETDATE()
Regards
Estuardo

Thanks Estuardo

Ya i guess your are right.


Thanks to all of your friends.

Thanks & Regards
Manish Kaushik

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.