Help formating output using alias names for column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help formating output using alias names for column

Author

Topic

SQL_Rookie
Starting Member

32 Posts

Posted - 2007-03-16 : 11:02:39

I have something like

select cast(Store_id as varchar(20))+' '+ CHAR(9) +
cast(Mod_dt as varchar(20))+' '+ CHAR(9) +
cast(MOd_by as varchar(20))+' '+ CHAR(9) + as 'StoreId ModifiedDate ModifiedUser'
from abc

I want the output to be format so the column heading and the columns line up under the heading so I can email it using xp_sendmail and the email will be easy to read without wraping of the row or misalignment of the columns like

STOREID ModifiedDate ModifiedBy
123 1/1/2007 Joe Smith
456 1/12/2007 Jane Smith

any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 11:07:18

[code]select convert(varchar, Store_id) + CHAR(9) +
convert(varchar, Mod_dt, 101) + CHAR(9) +
convert(varchar, Mod_by) AS 'StoreId' + CHAR(9) + 'ModifiedDate' + CHAR(9) + 'ModifiedUser'
from abc[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 11:11:37

I don't see why.
It is very easy to use rs.GetString function to do this for you...

Peter Larsson
Helsingborg, Sweden

SQL_Rookie
Starting Member

32 Posts

Posted - 2007-03-16 : 11:16:48

Peso,
I have tried that but the formating of the column name (AS StoreId..) is incorrect I get a syntax error....
Incorrect syntax near '+'.

I just don't know how to add the single quotes (') in the correct place....your query gets the same error...if you run it...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 11:19:27

[code]select convert(varchar, Store_id) + CHAR(9) +
convert(varchar, Mod_dt, 101) + CHAR(9) +
convert(varchar, Mod_by) AS 'StoreId ModifiedDate ModifiedUser'
from abc[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources