SQL Server: creating a column in query that combines two columns

Question

I'm pulling a query of demographic data for a list of current clients, specifically state of residence. I have our base customer data table that has some of the information then an additional view that is made up of appended data. I'm trying to create the column "state_residence" by merging the two data sets via left join but want the query to execute a if base_customer state is null then appended_data else base_customer.

In the past I used SAS and was able to do this but am unsure how to execute that query using MS SQL Server. Can anyone assist?

Answer 1

DECLARE @Customer TABLE (
    Customer varchar(100),
    Base_State varchar(2),
    Append_State varchar(2)
);
INSERT INTO @Customer VALUES
('Joe Smith', 'CO', NULL),
('Mike Johnson', 'CO', 'CO'),
('Stacy Blue', NULL, 'CO'),
('Penny G', NULL, 'FL'),
('Jimmy Dean', 'MT', NULL),
('Money Mike', NULL, NULL);

SELECT *, ISNULL(Base_State, Append_State) AS State_Residence
FROM @Customer;