SQL: Duplicate rows based on columns

Question

I have a table that looks like this:

Name   | Math   | English  | Arts
----------------------------------------
Brad   | 87     | 65       | 90
Julie  | 91     | 88       | 92

And I want to get:

Name  | Grade
--------------
Brad  | 87
Brad  | 65
Brad  | 90
Julie | 91
Julie | 88
Julie | 92

What's the simplest way to do that using SQL/Hive?

Answer 1

Something like this.

select name,math as Grade from your_table
union all
select name,English as Grade from your_table
union all
select name,Arts as Grade from your_table

Answer 2

select
  t.name, 
  CASE rows.col_name
    WHEN 'Math' THEN t.Math
    WHEN 'English' THEN t.**math** 
    WHEN 'Arts' THEN t.Arts
  end as Grade
from the_table t, 
     (select 'Math' as col_name
      union all 
      select 'English' as col_name
      union all 
      select 'Arts' as col_name) rows

Answer 3

You could use unpivot:

SELECT X.Name, X.Grade
FROM your_table s
UNPIVOT
(
  Grade
  FOR Subject in (Maths, English, Arts)
) X;

If you want to have the subject in the result add X.Subject into the select statement.