Search code examples
mysqlsqlexceltoad-data-point

Create a calculated column in SQL using CASE WHEN and IN

How can I create a new set of columns that searches a row for specific codes and flags a Yes or No based on the search criteria in Oracle SQL and MySQL?

Background:

I have a Customer code table that contains ~20+ columns with codes associated with the CustomerID record. Unfortunately, the columns are not tied to a specific code, as a result, each column can contain any code.

Customer Code 

CustomerID |col1|col2|col3|col4|col5|...|col20|
-----------------------------------------------
A          | 0   |  0 |  10| 19 | 200|...| 50 |
B          | 1   | 5  | 19 | 0  | 50 |...| 8  |
...

The goal is to create a subquery that contains several columns with the Customer ID that will indicate if the Customer has a specific code associated with them. For example, create a Code 10, Code 19, and Code 5 column and flag is as "Y" for each CustomerID if they have a code 10, 19, or 5 in any of the 20 columns in the table,

The result would look something like this.. (new columns in bold)

CustomerID |col1|col2|col3|col4|col5|...|col20| **Code 10**| **Code 19** | **Code 5**
------------------------------------------------------------------------------------
A          | 0   |  0 |  10| 19 | 200|...| 50 |  **Y**     |    **Y**    | **N**
B          | 1   | 5  | 19 | 0  | 50 |...| 8  |  **N**     |    **Y**    | **Y**
...

Current Process that works in Excel: In Excel, I would create a new column called "All Codes" and concatenate all the columns containing codes.

The new column would look like this:

|      All Codes      |
----------------------
|0 0 10 19 200 ... 50 | 
|1 5 19 0  50   ... 8 |

After creating a concatenate helper column, I then create a new column for each code I need using an if and contain text function. Results would look like below:

|    All Codes        | Code 10 | Code 19 | Code 5 
-------------------------------------------------------
|0 0 10 19 200 ... 50 |  Y      |  Y      | N
|1 5 19 0  50   ... 8 |  N      |  Y      | Y

Then I would remove the All Codes and join the new table with the CustomerID table.

Thank you for your time!

Solution

  • Use case and in:

    select t.*,
       (case when 10 in (col1, col2, . . .)
             then 'Y' else 'N'
        end) as cod_10,
       (case when 19 in (col1, col2, . . .)
             then 'Y' else 'N'
        end) as cod_19,
       (case when 5 in (col1, col2, . . .)
             then 'Y' else 'N'
        end) as cod_5
from t;

    The . . . is for the rest of the code columns.

    This is standard SQL and should work in any database.

    I would recommend that you change the data structure. You should have a table with one row per customerId and code, a customerCodes table.