CODEWITHSUNDEEP
sql
Zbigniew Kisły
Zbigniew Kisły

Reputation: 742

Combining 3 columns with boolean values into one

I've stumbled upon a table with three columns

id | isRed | isBlue | is Green
==============================
 1 |   1   |   0    |    0
 2 |   1   |   0    |    0
 3 |   1   |   0    |    0
 4 |   0   |   1    |    0
 5 |   0   |   1    |    0
 6 |   0   |   1    |    0
 7 |   0   |   0    |    1
 8 |   0   |   1    |    0
 9 |   0   |   0    |    1
10 |   0   |   0    |    0

I want to create query as simple as possible to transform it into something like that:

id | Color
==============================
 1 | red
 2 | red 
 3 | red  
 4 | blue
 5 | blue
 6 | blue
 7 | green
 8 | blue
 9 | green
10 | 0

The values can't be true in two different columns and I can't alter the database. I need it to append it to quite long query with as little complication as possible. Any ideas?

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

With a select and case:

select id,
       (case when isRed = 1 then 'red'
             when isBlue = 1 then 'blue'
             when isGreen = 1 then 'green'
             else '0'
        end) as color
from t;

Upvotes: 2

Related Questions