Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Format values as Percent Expand / Collapse
Author
Message
Posted Tuesday, August 26, 2008 3:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
Hi ALL,

SQL:

Select MyColumn FROM
(
SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
) MyTable
Order by 1 asc

Result:

MyColumn
1.10%
10.00%
12.00%
2.00%

Problem: values are not sorted

Question: Is it possible to sort values in ascending Order?

Thanks
Post #558696
Posted Tuesday, August 26, 2008 5:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:51 AM
Points: 1,260, Visits: 3,422
It is interesting it doesn't work with ROW_NUMBER() function , so how it works on the other way!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #558737
Posted Tuesday, August 26, 2008 5:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
They are sorted, they're just sorted as strings which is what the column is by the point the order by is evaluated. Perhaps something like this:

Select CAST(CAST(BaseColumn*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn FROM
(
SELECT 0.10 as BaseColumn
Union
SELECT 0.011 as BaseColumn
Union
SELECT 0.02 as BaseColumn
Union
SELECT 0.12 as BaseColumn
) MyTable
Order by BaseColumn asc




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #558753
Posted Tuesday, August 26, 2008 5:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:51 AM
Points: 1,260, Visits: 3,422
Gila you are very SQL Sick... very nice solution without adding or removing any other expression!

Nice solution Gila!

Dugi


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #558760
Posted Tuesday, August 26, 2008 5:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
Well I am expecting result in following order:

1.10%
2.00%
10.00%
12.00%

Post #558762
Posted Tuesday, August 26, 2008 5:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:51 AM
Points: 1,260, Visits: 3,422
I find it on another way like this but Gila's post is the best for me!?

Select MyColumn FROM
(
SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc



============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #558766
Posted Tuesday, August 26, 2008 5:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
SMAZ (8/26/2008)
Well I am expecting result in following order:

1.10%
2.00%
10.00%
12.00%



Which is exactly the order my query returns them in. Did you try it?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #558767
Posted Tuesday, August 26, 2008 6:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
Thanks Gail!

Its perfect.
Post #558772
Posted Tuesday, August 26, 2008 6:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:51 AM
Points: 1,260, Visits: 3,422
SMAZ (8/26/2008)
Thanks Gail!

Its perfect.


He is perfect also in other solutions as I can see ...!



============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #558777
Posted Thursday, August 28, 2008 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63

One more querstion on thsi:
Now the result is as follow:
1.10%
2.00%
10.00%
12.00%



Is it possible to implement some formatting on that values like:

1.10% = 01.10%
2.00% = 02.00%

10.00% = 10.00%
12.00% = 12.00%


Thanks
Post #560691
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse