SQL Server Change Value Of Column

I have a simple stored procedure that is pulling data for a SS report. One of the columns uses only a letter for the value. I would like to spell out the word the letter represents for the report. The procedure looks something like this...

ALTER PROCEDURE RPT_MYREPORT
{
@PID     INT=1234567
}

AS BEGIN

SELECT
    SSN,
    DOB,
    PID,
    Name,
    MaritalStatus
FROM
    Customers
END

Obviously theres more to it but thats the basic setup. Marital Status is either an "S" for Single开发者_如何学Python or "M" for Married. I would like to have these values spelled out for my report. Anyone know how?

Add a lookup table to your database containing the letter and the name/description you want to display. Join your main table to this in the query supplying the data to the report.

E.g. for the lookup table:

CREATE TABLE Marital_Status (
   marital_status_code char(1) PRIMARY KEY,
   marital_status_name char(7)
)

then add the lookup data

INSERT Marital_Status(marital_status_code, marital_status_name)
VALUES ('S', 'Single')
INSERT Marital_Status(marital_status_code, marital_status_name)
VALUES ('M', 'Married')

If you set the relationship between the main and lookup tables it also serves as data validation when new rows are inserted.

You have 2 options:

• Convert value to word inside SP (case MaritalStatus when 'S' then 'Single' when 'M' then 'Married' for example)
• Convert value to word inside report definition (expression based on iif function)