CODEWITHSUNDEEP
hivehiveql
sasi
sasi

Reputation: 364

how to select and organize the date from horizontal to vertical in hive

I have a table structure as below.

    Materialno  |   Rate    |   Month
    s1          |   20      |   jan
    s1          |   30      |   feb
    s1          |   40      |   mar
    s2          |   20      |   jan
    s2          |   30      |   feb

I need to query and convert table structure into below format

  Materialno    | jan_rate  |feb_rate   |mar_rate
        s1      |   20      |   30      |   40
        s2      |   20      |   30      |

is this possible in hive?

Upvotes: 1

Views: 123

Answers (1)

Alex Libov
Alex Libov

Reputation: 1491

Yes:

select Materialno, 
       max(if(Month='jan',Rate,0)) as jan_rate,
       max(if(Month='feb',Rate,0)) as feb_rate,
       max(if(Month='mar',Rate,0)) as mar_rate
       from inputTable group by Materialno;

This works assuming you have one rate value per month per material No', otherwise max will be chosen.

Upvotes: 1

Related Questions