How to transform Table in hive

Question

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..

Possible duplicate of [How to transpose/pivot data in hive?](https://stackoverflow.com/questions/23025380/how-to-transpose-pivot-data-in-hive) — Staza, Mar 18 '18 at 08:56

score 1 · Accepted Answer · answered Mar 18 '18 at 17:06

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;

How to transform Table in hive

1 Answers1