Group varying number of rows as columns in Hive ta

Question

I have a Hive table that contains userIDs and some variable choice, and basically looks like this:

userID    selection
   1          A
   1          D
   1          F
   2          A
   2          C

What I would like to do is condense this information and end up with something like:

 userID    selection1    selection2    selection3
    1          A             D              F
    2          A             C

Is this even possible? It isn't clear to me how to do this grouping, given that the number of possible selections varies with the user.

It would even be fine if I could do something like:

 userID    selection 
    1        A,D,F    
    2         A,C     

I have tried several approaches but so far nothing has been close enough to describe. I think what I want is something of the form:

select userID, group_concat(selection) from table_name group by userID

but as far as I can tell the group_concat function isn't available.

Thanks!

Answer 1

In case anyone ends up needing the answer to this, it can be achieved via:

select userID, collect_set(selection) from table_name group by userID