Replace and squeeze boolean feedback columns to single score column in BigQuery

I have data that looks like this (note one TRUE per row):

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
|ROWS| very_good | good  | neither | poor  | very poor |
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
| 1  | TRUE      | FALSE | FALSE   | FALSE | FALSE     |
| 2  | FALSE     | TRUE  | FALSE   | FALSE | FALSE     |
| 3  | FALSE     | FALSE | FALSE   | TRUE  | FALSE     |
|... | ...       | ...   | ...     | ...   | ...       |

I want to replace each TRUE with a number that depends on the column that it is in (5-1), so a score with very_good being 5 and very_poor being 1, and then squeeze it into one column. So it looks like this:

''''''''''''''
|ROWS| SCORE |
''''''''''''''
| 1  | 5     |
| 2  | 4     |
| 3  | 2     |
|... | ...   |

So far I’ve tried this:

SELECT
...,
(REPLACE(CAST(very_good = 'true' AS STRING),'true', '5'), 
REPLACE(CAST(good = 'true' AS STRING),'true', '4'),
REPLACE(CAST(neither = 'true' AS STRING),'true', '3'), 
REPLACE(CAST(poor = 'true' AS STRING),'true', '2'),
REPLACE(CAST(very_poor = 'true' AS STRING),'true', '1')) AS SCORE,
...,
FROM table

But this creates multiple columns and I can’t find a way to do multiple REPLACE’s within a single column, additionally, this doesn’t change the FALSE which needs to be removed. Ideally, I need to deal with some rows being filled with null values as well.

Answer

You can use a case expression:

select 
    rows, 
    case 
        when very_good = 'true' then 5
        when good      = 'true' then 4
        when neither   = 'true' then 3
        when poor      = 'true' then 2
        when very_poor = 'true' then 1
    end as score
from mytable

Note that for this to make sense, only one column in each row should be true – otherwise, the case expression returns the highest scoring column.

The following might also work in Big Query, if the columns are of boolean datatype:

select 
    rows, 
    case 
        when very_good then 5
        when good      then 4
        when neither   then 3
        when poor      then 2
        when very_poor then 1
    end as score
from mytable