CODEWITHSUNDEEP
sqlcase
ykjk
ykjk

Reputation: 85

Combining multiple Case statements into single output column

I need to re-categorise a column marketing_channel with 10 unique values into 15 distinct groups by matching certain criteria. I've done this via case statements but then the output is in 15 new columns.

  1. Is there a more elegant way to re-class the marketing_channel by simply adding 1 extra column like "marketing_sub_channel" that contains all new 15 classes?
  2. Is there better way to do the classification than by creating 15 case statements? Was thinking a with clause, but that would also be quite lengthy

enter image description here

Output looks like this but ultimately just a single added column would be great: enter image description here

Upvotes: 0

Views: 5631

Answers (1)

Tony
Tony

Reputation: 109

Yes you just have to change the format a bit. Remove the "case" statement at the beginning of each line and just put the "End" at the end of the statement, like so :

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE null
END as marketing_sub_channel

or in your case:

CASE
    WHEN medium like ('%affiliate%') or marketing_cannel ='Affiliates' then 'Affiliate'
    WHEN campaign like ('%_Display brand_global Progromatic Display%') then 'Dispay' 
    WHEN campaign like ('%display%') and campaign not like ('progrommatic') then 'Dispay'
    ....
    else null
END as marketing_sub_channel

Also I would like to note that in your case statement since you have '%display%' and '%_Display brand_global Progromatic Display%' that you place the longer more specific one on top so it can trigger if it needs to. If '%display%' is on top then it will always trigger first since it contains a substring of the other one.

Upvotes: 1

Related Questions