hive列转行

一、问题

hive如何将

变为：

a       b       ,,

c       d       ,,

test.txt

cat column_row.txt

a,b,1

a,b,2

a,b,3

c,d,4

c,d,5

c,d,6

create table tmp.column_row

(

  col1 string,

  col2 string,

  col3 string

)

row format delimited fields terminated by ','

stored as textfile;

load data local inpath '/tmp/column_row.txt' into table tmp.column_row;

select col1,col2,concat_ws(',',collect_set(col3))

from tmp.column_row

group by col1,col2;

a       b       2 ,3 ,1

c       d       6,4 ,5