CODEWITHSUNDEEP
sqlsql-server
Don
Don

Reputation: 3

combining boolean results from two columns SQL

I have the following example data from two Boolean columns:

ID      Male      Female
1        1          0
2        0          1
3        0          1
4        1          0
5        0          1

I would like to combine the two columns into a single column containing just 'M' and 'F'. Also, I would preferably like to do it in the SELECT statement I am writing while defining the column.

The result then should be something like:

ID     Gender
1        M
2        F
3        F
4        M
5        F

I know I could achieve this with separate update statements like:

UPDATE table_1
SET Gender='M'
FROM table_2 t2
WHERE t2.Male=1

and

UPDATE table_1
SET Gender='F'
FROM table_2 t2
WHERE t2.Female=1

But I was really hoping to achieve the same result while declaring the Gender column?

Does anyone know if this is possible?

Thanks in advance!

Upvotes: 0

Views: 3178

Answers (4)

kjmerf
kjmerf

Reputation: 4345

Here is how to do it with a SELECT statement:

SELECT ID,
CASE WHEN Male = 1 THEN 'M'
ELSE 'F' END AS gender
FROM My_Table

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

UPDATE table_1
SET Gender= CASE WHEN Male = 1 THEN 'M'
                 WHEN Female = 1 THEN 'F'
                 ELSE 'Other' // optional
            END;

Of course im trying to be open mind and guess you allow Male = 0 and Female = 0 otherwise you can simplify with IIF

UPDATE table_1
SET Gender = IIF ( Male = 1, 'M', 'F' );

Upvotes: 1

shakedzy
shakedzy

Reputation: 2893

How about select if(Male = 1, 'M', 'F') as gender?

Upvotes: 0

Ullas
Ullas

Reputation: 11556

You can use a CASE expression.

Query

UPDATE t1
SET t1.Gender = (
    CASE WHEN t2.Male = 1 AND t2.Female = 0 THEN 'M'
    WHEN t2.Male = 0 AND t2.Female = 1 THEN 'F'
    ELSE NULL END
)
FROM Table_1 t1
JOIN Table_2 t2
ON t1.ID = t2.ID;

Upvotes: 2

Related Questions