CODEWITHSUNDEEP
sqlhiveql
shakedzy
shakedzy

Reputation: 2893

SQL: Duplicate rows based on columns

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?

Upvotes: 0

Views: 94

Answers (3)

Vincent Pan
Vincent Pan

Reputation: 256

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.

Upvotes: 1

StanislavL
StanislavL

Reputation: 57381

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

Upvotes: 1

Utsav
Utsav

Reputation: 8093

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

Upvotes: 1

Related Questions