Pivot with non unique IDs

SteeeevSteeeev Vertica Customer

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?

Tagged:

Best Answer

  • marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    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
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file