Solved

How to substitute text in the select statement output?

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

15 Experts available now in Live!

Get 1:1 Help Now