Using SQL CASE statement to convert TEXT to BOOLEAN value

Question

is it possible to change multiple field values when using CASE statement?

I have a Text field with several values ( 5 differnt ones) and each of this value has its own Boolean field in tragt Data Extension.

Is there a way to combine those CASE statements? I dont get it working.

I used this as a basis:

SELECT
 Key,

, case 
    when Source in ('Web') then 1 
    else 0 
  end As Source_Web
, case 
    when Source in ('facebook') then 1 
    else 0 
 end As Source_facebook
FROM [Old_DE]

This would continue until all Values are fetched but it is not working. Anyone able to help out?

UPDATE

Let me elaborate more on it:

OLD DE:

Key, Country, Source
123456,SW,Phone
123456,US,Web
123456,IT,Phone
123456,Clara,facbook

New Data Extension has structure below (source fields are boolean fields and should updated to true(

Outcome of the query should look like:

Key,Source_Phone,Source_Web,Source_facebook
123456,1,1,1

hope that makes my problem more clear

Answer 1

Let break this down in to steps:

---

First, start by giving each record a value of 1 in the column where where applicable

SELECT
    Key,
    IIF(Source = 'Phone',1,0) as Source_Phone,
    IIF(Source = 'Web',1,0) as Source_Web,
    IIF(Source = 'facebook',1,0) as Source_facebook

FROM [Old_DE]

Based on your example above, you'd get a result set as such

Key   |Source_Phone|Source_Web|Source_facebook
------+------------+----------+---------------
123456|     1      |    0     |      0
123456|     0      |    1     |      0
123456|     1      |    0     |      0
123456|     0      |    0     |      1

---

Secondly, you want to combine the the records to get an aggregate count of applicable records.

SELECT
    Key,
    SUM(IIF(Source = 'Phone',1,0)) as Source_Phone,
    SUM(IIF(Source = 'Web',1,0)) as Source_Web,
    SUM(IIF(Source = 'facebook',1,0)) as Source_facebook

FROM [Old_DE]

GROUP BY Key

which will result in

Key   |Source_Phone|Source_Web|Source_facebook
------+------------+----------+---------------
123456|     2      |    1     |      1

---

Lastly, since you're looking to insert as boolean, you need to address numbers greater than 0. That's just another IF to transform any number > 0 in to 1.

SELECT
    Key,
    IIF(SUM(IIF(Source = 'Phone',1,0)) > 0, 1, 0) as Source_Phone,
    IIF(SUM(IIF(Source = 'Web',1,0)) > 0, 1, 0) as Source_Web,
    IIF(SUM(IIF(Source = 'facebook',1,0)) > 0, 1, 0) as Source_facebook

FROM [Old_DE]

GROUP BY Key

which will result in your final desired set which will insert into your boolean fields properly.

Key   |Source_Phone|Source_Web|Source_facebook
------+------------+----------+---------------
123456|     1      |    1     |      1