Solved

How to substitute text in the select statement output?

Posted on 2011-09-08
2
227 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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

947 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now