0

I have some data that looks something like this:

key B
1   true
1   false
2   false
2   false

And I want to roll it up so that if column B has two false values then the resulting value for B is false; otherwise, if there is a true and false value the value for B is true. Like this:

key B
1   true
2   false

Is there a way that this can be done with (Snowflake) SQL? I first thought of a pivot table but this doesn't quite seem to be what pivots do.

  • @McNets the Snowflake docs say: "Snowflake supports most of the commands and statements defined in SQL:1999." But I'm not sure how to tag that. – fraxture Oct 14 at 17:45
  • 1
    It doesn't matter,let it as [snowflake] – McNets Oct 14 at 17:48
1

According to your text if any of them are true is must be true, otherwise is false:

select
    key,
    case 
      when sum(case when B is true then 1 else 0 end) > 0 then true
      else false
    end as result
from
   t
group by
    key;

db<>fiddle here

  • this doesn't seem to do exactly what you intended, unless I'm mistaken. In the db<>fiddle you provided, the result column contains the sum value not true or false. – fraxture Oct 14 at 17:43
  • 1
    No, in fact it returns 1=true, 0=false, binary representation not the literals 'true' and 'false', but it is due to MySQL. Have a look: stackoverflow.com/questions/3228094/… – McNets Oct 14 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.