Smash125 (9/19/2014)
LEFT(CAST(ROUND([Val],0,0) as nvarchar(10)),2) + '%' AS [val]to get the results some like this
34.100 34.000 ->34%
39.800 40.000 ->40%
35.400 35.000 ->35%
But this does not holds good when the percentage is 100% this holds when the percentage two characters. Can any body guide how to solve this
The best way to solve this is leave the formatting to the front end. You should pass numeric values in this case to the front end. There the % can be added. If you turn everything into formatted strings the front end can't accurately sort or do any types of calculations without first removing the formatting.
--EDIT--
And why would you use nvarchar? Last time I checked all values for numbers will fit easily in the standard ASCII set so no need for the extended characters here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/