SQL to pivot columns to rows

-- Create a sample table 

create multiset table mydbname.stscorecard

(

    id integer,

    sname varchar(20),

    Math_score integer,

    Science_score integer,

    Social_score integer

) primary index(id)

;

insert into mydbname.stscorecard

(id,sname,Math_score, Science_score, Social_score)

values(1,'Student1', 91,92,93)

;

insert into mydbname.stscorecard

(id,sname,Math_score, Science_score, Social_score)

values(2,'Student2', 94,95,96)

;

-- Following query demonstrates how to pivot a single row into multiple rows

-- Assumption here is number of columns to pivot to rows is costant number. 

-- In this example as you see we have 3 subjects only.

select * from 

(

select id,sname,'Maths'(varchar(20)) as subject, Math_score as Score 

from mydbname.stscorecard

union all

select id,sname,'Science' as subject, Science_score as Score

from mydbname.stscorecard

union all

select id,sname,'Social' as subject, Social_score as Score

from mydbname.stscorecard

) as src

order by id, subject

;

-- Pivoted rows are

  1,Student1,Maths,91

  1,Student1,Science,92

  1,Student1,Social,93

  2,Student2,Maths,94

  2,Student2,Science,95

  2,Student2,Social,96