I have the following dataset in Hive, and I would like to transpose rows into columns.

I would like to have a new table after transpose that shows only one line by a customer and multiple columns by Status, e.g.

I tried some examples I've found on the Internet, but I could not make it works. Here, for the sake of simplicity, I listed only three statuses, but in fact, I could have more than that.

In SAS, I used to did something such as the following:

proc transpose
   data = imputtable;
   out = outputtable;
   by customer;
   id status;
   var quantity;
run;

SAS gets all the existing statuses and pivots them into columns. I was looking to do the same in Hive.

Regards,

Marcio

CodePudding user response：

Use conditional aggregation:

select Customer, 
       sum(case when Status = 'Paid'   then Quantity else 0 end) as Paid     ,
       sum(case when Status = 'N Paid' then Quantity else 0 end) as `N Paid` ,
       sum(case when Status = 'Open'   then Quantity else 0 end) as Open
 from table
group by Customer