How to avoid duplicates with case statement

December 3, 2021

I have a table with the following structure (customer id and product):

I’m trying to pivot the same, in order to have just one line per Id.
To do this, I’m using case statements:

select distinct
id,
case when purchased_item = 'Item 1' then 'Y' else 'N' end item_1,
case when purchased_item = 'Item 2' then 'Y' else 'N' end item_2,
case when purchased_item = 'Item 3' then 'Y' else 'N' end item_3
from my_table

However, this is the result I get:

Is there any way to get the same result but with just one single line per Id?

>Solution :

to answer your problem , you need to group by and use max fucntion:

select 
  id,
  max(case when purchased_item = 'Item 1' then 1 else 0 end) item_1,
  max(case when purchased_item = 'Item 2' then 1 else 0 end) item_2,
  max(case when purchased_item = 'Item 3' then 1 else 0 end) item_3
from my_table
group by id