BigQuery SQL: How to use different columns in CASE expression-CodePudding

Input Data:

columnA  columnB
true     false
true     true
false    false
false    true

Problem Statement: From above mentioned data, I want to use different columns to get the result.

Expected Output:

columnA  columnB   result
true     false     A
true     true      B
false    false     C
false    true      C

Tried SQL Query:

SELECT
columnA, 
columnB,
CASE columnA WHEN 'true' AND columnB ='false' THEN 'A'
             WHEN 'true' AND columnB ='true' THEN 'B'
             ELSE 'C' END AS result

It seems unable to use different columns in CASE expression. Is there any solution?

CodePudding user response：

Yes you can use different columns but, you need to rewrite your query

SELECT
columnA, 
columnB,
CASE WHEN columnA  = 'true' AND columnB ='false' THEN 'A'
             WHEN columnA  = 'true' AND columnB ='true' THEN 'B'
             ELSE 'C' END AS result
FROM mytable

CodePudding user response：

Consider below "version"

select *,
  case (columnA, columnB) 
    when (true, false) then 'A'
    when (true, true) then 'B'
    else 'C'
  end result
from your_table

if applied to sample data in your question - output is