substitution

4 Nov 2005 7:46 PM

rodchar

hey all,

if i have a integer status column like the following:
Status
1
2
3

How do i substitute a descriptive string in place of the numbers in a
resultset?

1=Unchanged
2=Updated
3=Underwriting

thanks,
rodchar

4 Nov 2005 7:51 PM

Itzik Ben-Gan

One option is to create a lookup table.

Another, is inline translation with a CASE expression:

SELECT status,
CASE status
    WHEN 1 THEN 'Unchanged'
    WHEN 2 THEN 'Updated'
    WHEN 3 THEN 'Underwriting'
END AS statusdesc
FROM T1;

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Join us for the SQL Server 2005 launch at the SQL Week in Israel!
http://www.microsoft.com/israel/sql/sqlweek/default.mspx

Show quote

"rodchar" <rodc***@discussions.microsoft.com> wrote in message
news:80293258-30DC-4D6A-A32E-7B4DA0A40BB2@microsoft.com...
> hey all,
>
> if i have a integer status column like the following:
> Status
> 1
> 2
> 3
>
> How do i substitute a descriptive string in place of the numbers in a
> resultset?
>
> 1=Unchanged
> 2=Updated
> 3=Underwriting
>
> thanks,
> rodchar

4 Nov 2005 8:11 PM

rodchar

Thanks everyone, this helped a lot.
Rodchar

Show quote

"Itzik Ben-Gan" wrote:

> One option is to create a lookup table.
>
> Another, is inline translation with a CASE expression:
>
> SELECT status,
>   CASE status
>     WHEN 1 THEN 'Unchanged'
>     WHEN 2 THEN 'Updated'
>     WHEN 3 THEN 'Underwriting'
>   END AS statusdesc
> FROM T1;
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> Join us for the SQL Server 2005 launch at the SQL Week in Israel!
> http://www.microsoft.com/israel/sql/sqlweek/default.mspx
>
>
> "rodchar" <rodc***@discussions.microsoft.com> wrote in message
> news:80293258-30DC-4D6A-A32E-7B4DA0A40BB2@microsoft.com...
> > hey all,
> >
> > if i have a integer status column like the following:
> > Status
> > 1
> > 2
> > 3
> >
> > How do i substitute a descriptive string in place of the numbers in a
> > resultset?
> >
> > 1=Unchanged
> > 2=Updated
> > 3=Underwriting
> >
> > thanks,
> > rodchar
>
>
>

4 Nov 2005 7:57 PM

JT

I assume you know how to perform a join to a related table containing
descriptions. When combining an integer value with a varchar into one
varchar value, you will need to use the Convert function like so:

select
    convert(varchar(2),a.status) + b.description as StatusDesc,
    . . .
    . . .

Show quote

"rodchar" <rodc***@discussions.microsoft.com> wrote in message
news:80293258-30DC-4D6A-A32E-7B4DA0A40BB2@microsoft.com...
> hey all,
>
> if i have a integer status column like the following:
> Status
> 1
> 2
> 3
>
> How do i substitute a descriptive string in place of the numbers in a
> resultset?
>
> 1=Unchanged
> 2=Updated
> 3=Underwriting
>
> thanks,
> rodchar