'How do I transpose rows into columns

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



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source