I am new to SQL and working on a database that needs a binary indicator based on the presence of string values in a column. I'm trying to make a new table as follows:

Original:

Desired:

SQL code:

SELECT 
      ID, 
      Contract, 
      Indicator,
      CASE 
         WHEN Indicator IN ('a', 'b')
         THEN 1
         ELSE 0
      END as Type
INTO new_table
FROM old_table

The table I keep creating reports every type as 0.

I also have 200 distinct indicators, so it will be really time-consuming to write each as:

CASE 
   WHEN Indicator = 'a' THEN '1'
   WHEN Indicator = 'b' THEN '1'

Is there a more streamlined way to think about this?

Thanks!

CodePudding user response：

I think the first step is to understand why your code doesn’t work right now.

If your examples of what’s Indicator column are literally the strings you noted (a, b, c in one string and c, d, e in another) you should understand that your case statement is saying “I am looking for an exact match on the full value of Indicator against the following list -

1. The letter A or
2. The letter B

Essentially- you are saying “hey SQL, does ‘a,b,c’ match to ‘a’? Or does ‘a,b,c’ match to ‘b’. ?”

Obviously SQL’s answer is “these don’t match” which is why you get all 0s.

You can try wildcard matching with the LIKE syntax.

Case when Indicator like ‘%a%’ or Indicator like ‘%b%’ then 1 else 0 end as Type

Now, if the abc and cde strings aren’t REALLY what’s in your database then this approach may not work well for you.

Example, let’s say your real values are words that are all slapped together in a single string.

Let’s say that your strings are 3 words each.

1. Cat, Dog, Man
2. Catalog, Stick, Shoe
3. Hair, Hellcat, Belt

And let’s say that Cat is a value that should cause Type to be 1.

If you write: case when Indicator like ‘