Pivot with non unique IDs

Steeeev

I have data that looks like

ID	FRUIT
1	Pear
1	Orange
2	Apple
2	Pear

And I want to get to

ID	Pear	Orange	Apple
1	Y	Y	N
2	Y	N	Y

I looked at https://forum.vertica.com/discussion/238733/how-do-i-pivot-in-vertica but still stuck. Can anyone help?

marcothesane

The query should be this:

SELECT
  id
, (MAX(CASE fruit WHEN 'Pear' THEN counter   END) IS NOT NULL)  AS Pear
, (MAX(CASE fruit WHEN 'Orange' THEN counter END) IS NOT NULL)  AS Orange
, (MAX(CASE fruit WHEN 'Apple' THEN counter  END) IS NOT NULL)  AS Apple
FROM w_counter
GROUP BY
  id
-- out id|Pear |Orange|Apple
-- out  1|true |true  |false
-- out  2|true |false |true