BigQuery SQL: How to use different columns in CASE expression

peggy 0
Viewed 40

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?

google-bigquery sql
2 Answers

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
nbk0

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

enter image description here

Mikhail Berlyant0
Related