$sign in query results - SQL Server Forums

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

Transact-SQL (2005)

$sign in query results

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2008-08-22 : 14:46:37

How can i get $sign in my results..

as i have 3columns:

Qty Total Unit
---------------------------------------
100.00000 280.000000000 2.80000
450.00000 225.000000000 0.50000
200.00000 100.000000000 0.50000

but i want like:
Qty Total Unit
---------------------------------------
100 $280.00 $2.80
450 $225.00 $0.50
200 $100.00 $3.20

what should i do to get this?

Thank you for your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-22 : 15:00:28

If you are using some kind of front-end to present this data, format the values there. The only other way would be to convert the value to a character string which has a lot of disadvantages.

Be One with the Optimizer
TG

ayu
Starting Member

43 Posts

Posted - 2008-08-22 : 15:02:36

thanks for your reply.

But no, i m using in back-end sql.. i have to use this results in excel spreadsheet.

can you help me now?

thanks.

ayu
Starting Member

43 Posts

Posted - 2008-08-22 : 15:05:03

i done so far like:

select convert(Int,quantity) as QTY,convert(decimal(6,2),(quantity * unitprice)) as Total,
convert(decimal(5,2),unitprice) as Unit from quanttab

it gives me error like:

Arithmetic overflow error converting numeric to data type numeric.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-22 : 15:41:58

So Excel is your front-end, right? It formats numeric values very well - better than sql server why don't use simply return the native value and format it in Excel?

EDIT:
The problem with formatting it in the sql query is that you need to convert the values to character strings. That is slow. It also means you loose the ability to treat the values as numeric values for calculations, ordering, etc.

In case you won't be satisfied without doing it

here is one way:



select convert(decimal(15, 0), Qty) as qty
       ,'$' + convert(varchar(15), convert(decimal(12, 2), total)) as total
       ,'$' + convert(varchar(9), convert(decimal(6, 2), unit)) as unit
from   ( 
       select 100.00000 as qty, 280.000000000 as total, 2.80000 as unit union all
       select 450.00000 , 225.000000000, 0.50000 union all
       select 200.00000 , 100.000000000, 0.50000
       ) as yourTable

output:
qty                                     total                           unit
--------------------------------------- ------------------------------- -------------------------------
100                                     $280.00                         $2.80
450                                     $225.00                         $0.50
200                                     $100.00                         $0.50

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-23 : 03:40:48

quote:
Originally posted by ayu

thanks for your reply.

But no, i m using in back-end sql.. i have to use this results in excel spreadsheet.

can you help me now?

thanks.

Export data to EXCEL as they are
Format the cell to have currency format in EXCEL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources