CODEWITHSUNDEEP
sqloracle-database
AaronThomson
AaronThomson

Reputation: 763

Merge three boolean columns into one enumerated column

I am migrating data from a DB with a table containing 3 boolean columns which are, in concept, mutually exclusive. eg:

| TypeA | TypeB | TypeC |
| 0     | 1     | 0     |
| 0     | 0     | 1     |
| 0     | 1     | 0     |
| 1     | 0     | 0     |
| 1     | 0     | 0     |

I need to merge these three booleans into a single column based on which of the 3 boolean columns is set to true. The result I'm after would look something like:

| Type |
| B    |
| C    |
| B    |
| A    |
| A    |

So far, I have come up with the following, however it feels a bit messy. Is there a better way?

SELECT
CASE WHEN TypeA= 1
   THEN 'A'
   ELSE
      CASE WHEN TypeB= 1
           THEN 'B'
           ELSE
              CASE WHEN TypeC= 1
                   THEN 'C'
              END
      END
END as Type
FROM TABLE

Upvotes: 2

Views: 497

Answers (2)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23747

chr(65 + TypeB + 2*TypeC) as Type

Another solution: 

decode(TypeB-TypeC, 0, 'A', 1, 'B', 'C') as Type

Upvotes: 2

Kyle Hale
Kyle Hale

Reputation: 8120

You can stack your conditionals in a CASE statement like so

CASE
WHEN TypeA = 1 THEN 'A'
WHEN TypeB = 1 THEN 'B'
WHEN TypeC = 1 THEN 'C'
END as Type

Upvotes: 8

Related Questions