Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to substitute text in the select statement output?

Posted on 2011-09-08
2
Medium Priority
?
240 Views
Last Modified: 2012-08-14
I have only numeric values in a table.  I want the output of a select statement to show text values rather than the numeric values.  Modifying the table is not an option.  I need to perform the substitution entirely within the select statement.

Select Example:

select period, sum(sales)
from salestable

Desired Output Example:

Apr  23,000
May 50,000


Table Data Example:

Period Sales
4          23,000
5          50,000

The actual data is much more complex, but a solution to the above should do it.  Thanks in advance!
0
Comment
Question by:CPA_MCSE
2 Comments
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 36503900
Select Case When Period = '4' Then 'Apr'
                     When Period = '5' Then 'May'
                     End As Period,
Sales
From salestable


You can modify the case statement for your other months.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36505500


use convert and convert styles
convert(datatype,expression,style)

e.g.

select left(datename(mm,'2001'+right('00'+convert(varchar(2),period),2)+'01'),3) as Period
       ,convert(money,sales,1) as Sales
from (
select period,sum(sales) as sales
from salestable
group by period) as x
order by x.period

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question