0

I have a table of interactions data of which there can be multiple interactions per customer. Each interaction is on a separate row in the table, and has an interaction_code which relates to a specific interaction type.

I want to know which unique customers have made an interaction either by phone, online, or both (phone interaction_code = 101, 102 & 107, online interaction_code = 113, 152)

I have used a CASE statement to create new columns names 'Phone' & 'Online' and have populated with a 1 or 0 depending on the interaction type.

However, if a customer has made an interaction on the phone and online, there are two entries for the customer, one line with a '1' in the Phone column, and another with a '1' in the Online column. (There can be multiple entries if a customer has made multiple phone and online interactions - but each interaction is still on a different row)

What I would like to see is one row per customer, that shows if that customer has made either a phone interaction, an online interaction, or both

This is the code I have used:

  SELECT customer_id, 
    CASE
      WHEN interaction_code = 113 THEN 1
      WHEN interaction_code = 152 THEN 1
      ELSE 0
    END AS Online,
    CASE
      WHEN interaction_code = 101 THEN 1
      WHEN interaction_code = 102 THEN 1
      WHEN interaction_code = 107 THEN 1
      ELSE 0
    END AS Phone
   FROM interactions
    WHERE interaction_code = 113
      OR interaction_code = 152
      OR interaction_code = 101
      OR interaction_code = 102
      OR interaction_code = 107
    ORDER BY customer_id;

This is a sample of the data

This is an example of the results I am getting

This is an example of the desired output

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
user17995047
  • 3
  • 2
  • Sample data and desired output would help us help you. – Littlefoot Jan 25 '22 at 19:21
  • Hi,Thanks for the reply. I've just posted some example pictures – user17995047 Jan 25 '22 at 21:39
  • Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) – Jon Heller Jan 26 '22 at 04:49
  • @user17995047 You were very close, you just need to add an aggregation function in front of your case. For example, add a `MAX` and then also add a `GROUP BY` clause. You may also want to use `WHEN interaction_code in (113,152)` instead of listing each value with a separate `WHEN` clause. – Jon Heller Jan 26 '22 at 04:51

1 Answers1

1

Going off of what Jon Heller said in the comments, you can use a query like the one below to achieve your result.

  SELECT customer_id,
         MAX (CASE WHEN interaction_code IN (113, 152) THEN 1 ELSE 0 END)          AS has_online,
         MAX (CASE WHEN interaction_code IN (101, 102, 107) THEN 1 ELSE 0 END)     AS has_phone
    FROM interactions
GROUP BY customer_id
ORDER BY customer_id;
EJ Egyed
  • 5,791
  • 1
  • 8
  • 23