Skip to main content
Question

Pivot with non unique IDs

  • July 19, 2022
  • 1 reply
  • 5 views
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?

    1 reply

    marcothesane
    Forum|alt.badge.img+1
    • marcothesane
    • Participating Frequently
    • July 19, 2022

    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
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement