How to pivot data in Hive?

Question

First, I've checked other topics on the subject like this one How to transpose/pivot data in hive? but that doesn't match with what I want.

So this is the table I have

 | ID   |   Day    |  Status   | 
 | 1    |    1     |     A     |
 | 2    |    10    |     B     |
 | 3    |    101   |     A     |
 | 3    |    322   |     B     |
 | 3    |    102   |     C     |
 | 3    |    354   |     D     |

And i'd like to concat the different Status for each IDs ordering by the Day, in order to have this :

 | ID   |  Status   | 
 | 1    |     A     |
 | 2    |     B     |
 | 3    |  A,C,B,D  |

The thing is that I don't know how many status I can have, so i can't create as many columns I want for the days since I don't know how many day/status I'll have, so the answers from other topics with group_map or others, I don't know how to adapt it for my problem.

Thank's for helping me ^^

Answer 1

use collect_set (for distinct values) or collect_list to aggregate array and concatenate it using concat_ws:

select ID, concat_ws(',',collect_list(Status)) as Status 
  from table 
 group by ID;