Easy table transpose in hive

问题:

I need to transpose my table. Now i have that type of table:

Atr_1|Atr_2
 A   | 1
 A   | 2

But i want to get the next result

Atr_1|Atr_2|Atr_3
 A   | 1   |  2

How should i transpose my table for achieving this result?

回答1:

Use case statements with min() or max() aggregation:

select Atr_1,
       max(case when Atr_2=1 then 1 end ) Attr_2,
       max(case when Atr_2=2 then 2 end ) Attr_3
  from table t
 group by Atr_1;

回答2:

If you have only two values, min() and max() do what you want:

select atr_1, min(atr_2) as atr_2, max(atr_3) as atr_3
from t
group by atr_1;

回答3:

I think you want aggregation :

SELECT atr_1, 
       MAX(CASE WHEN SEQ = 1 THEN atr_2 END) as atr_2,
       MAX(CASE WHEN SEQ = 2 THEN atr_2 END) as atr_3
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY atr_1 ORDER BY atr_2) AS SEQ
      FROM table t
     ) t
GROUP BY atr_1;