February 12, 2014 at 9:39 pm
Hi,
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
February 12, 2014 at 10:38 pm
Try this
DECLARE @dollar TABLE (id INT IDENTITY(1,1),Cost INT)
INSERT INTO @dollar
( Cost )
VALUES (-4255),(25454),(467834)
select ID, GETDATE() as 'Date', REPLACE('$' + CONVERT(VARCHAR(20),CONVERT(MONEY,SUM(Cost))),'$-','-$') Dollars
, REPLACE('$' + CONVERT(VARCHAR(20),MAX(CONVERT(MONEY,COST))),'$-','-$') Funding
from @dollar
GROUP BY ID;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 12, 2014 at 10:43 pm
If you were to be using SQL 2012 or 2014, then this would work
DECLARE @dollar TABLE (id INT IDENTITY(1,1),Cost INT)
INSERT INTO @dollar
( Cost )
VALUES (-4255),(25454),(467834)
select ID, GETDATE() as 'Date'
, FORMAT(SUM(Cost), 'C', 'en-us') AS Dollars
,FORMAT(MAX(Cost),'C','fr') AS Funding --fr to see euros/french, en-gb to see pounds/UK
FROM @dollar
GROUP BY ID;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 12, 2014 at 11:55 pm
It worked. Thanks a lot.
February 13, 2014 at 2:59 am
Thanks SQLRNNR, Very informative.
Please explain the difference of usage of REPLACE in your first post.
Also, why you used '$-','-$' in the query.
February 13, 2014 at 6:23 am
Junglee_George (2/13/2014)
Thanks SQLRNNR, Very informative.Please explain the difference of usage of REPLACE in your first post.
Also, why you used '$-','-$' in the query.
It is to move the minus sign to the left of the dollar sign
ie changes $-4255.00 to -$4255.00
Far away is close at hand in the images of elsewhere.
Anon.
February 13, 2014 at 8:26 am
David Burrows (2/13/2014)
Junglee_George (2/13/2014)
Thanks SQLRNNR, Very informative.Please explain the difference of usage of REPLACE in your first post.
Also, why you used '$-','-$' in the query.
It is to move the minus sign to the left of the dollar sign
ie changes $-4255.00 to -$4255.00
Thanks for following up on that.:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 13, 2014 at 8:28 am
Junglee_George (2/13/2014)
Thanks SQLRNNR, Very informative.Please explain the difference of usage of REPLACE in your first post.
Also, why you used '$-','-$' in the query.
You are welcome.
David answered the $ question. I am uncertain what you mean by "difference of usage of replace." Could you clarify? Or was it just about the $? The replace is necessary to help with the position of the $ as David pointed out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 13, 2014 at 8:29 am
vigneshkumart50 (2/12/2014)
It worked. Thanks a lot.
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 13, 2014 at 8:40 am
SQLRNNR (2/13/2014)
David Burrows (2/13/2014)
Junglee_George (2/13/2014)
Thanks SQLRNNR, Very informative.Please explain the difference of usage of REPLACE in your first post.
Also, why you used '$-','-$' in the query.
It is to move the minus sign to the left of the dollar sign
ie changes $-4255.00 to -$4255.00
Thanks for following up on that.:cool:
Your welcome
About time I contributed something useful for a change
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy