ROUND by another column - 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 2008 Forums

Transact-SQL (2008)

ROUND by another column

Author

Topic

danielsaf
Starting Member

2 Posts

Posted - 2011-09-30 : 14:07:19

Hello,

Is it possible to do a SELECT of values and ROUND the result according to another column in the table.

For example: a table of test codes, result and 'decimal_precision'.
01 2.3456789 2
02 10.8456678 1
03 6.123458 3

The query should show the results rounded according to the 3rd column, i.e.: 2.35, 10.8, 6.123

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-30 : 14:26:51

SELECT test_code, ROUND(result, decimal_precision) FROM myTable

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-30 : 14:33:48

I have a feeling you are talking about the presentation format as opposed to the numeric precision though, right?
ie you don't want this do you:
2.3500000
10.8000000
6.1230000

you want different numeric types for each row:
2.35 numeric(3,2)
10.8 numeric(3,1)
6.123 numeric(4,3)

that should really be done in the application you use to present the data. If you need to do it in sql you would have to convert the values to varchar - ugh.

EDIT:
or this might do it:
select convert(float, round(result, decimal_precision)) as [roundedResult2]

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-30 : 14:54:57

There's also:

SELECT test_code, LTRIM(STR(result, 10, decimal_precision)) FROM myTable

danielsaf
Starting Member

2 Posts

Posted - 2011-10-01 : 16:52:45

Thanks all,

TG's code in the end seems to do the trick, i.e. the:

select convert(float, round(result, decimal_precision)) as [roundedResult2]

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources