CODEWITHSUNDEEP
hive
HEMANT PATEL
HEMANT PATEL

Reputation: 77

How to transform Table in hive

I have a table in Hive in below format.

std_id  std_name sub_id sub_name marks 
1       xxx      123    Eng      70
1       xxx      125    Maths    90
1       xxx      124    Science  80

I want a table in below format as output.

std_id std_name Eng Science Maths  
1      xxx      70  80      90

how can i get the output with using Hive Query..

Upvotes: 2

Views: 448

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Use MAX ( CASE.. ) with GROUP BY 

SELECT std_id
    ,std_name
    ,MAX(CASE 
            WHEN sub_name = 'Eng'
                THEN marks
            END) AS Eng
    ,MAX(CASE 
            WHEN sub_name = 'Science'
                THEN marks
            END) AS Science
    ,MAX(CASE 
            WHEN sub_name = 'Maths'
                THEN marks
            END) AS Maths
FROM t
GROUP BY std_id
    ,std_name;

Upvotes: 1

Related Questions