SQL to pivot rows to columns
-- Create a sample table
create multiset table mydbname.stscorecard
(
id integer,
sname varchar(20),
subject varchar(20),
score integer
) primary index(id)
;
-- Insert sample data for student1
insert into mydbname.stscorecard
(id, sname, subject, score)
values (1,'student1','Maths',90);
insert into mydbname.stscorecard
(id, sname, subject, score)
values (1,'student1','Science',91);
insert into mydbname.stscorecard
(id, sname, subject, score)
values (1,'student1','Social',99);
-- Insert sample data for student2
insert into mydbname.stscorecard
(id, sname, subject, score)
values (1,'student2','Maths',92);
insert into mydbname.stscorecard
(id, sname, subject, score)
values (1,'student2','Science',93);
insert into mydbname.stscorecard
(id, sname, subject, score)
values (1,'student2','Social',94);
-- Follwoing query demonstrates how we can pivot rows to columns
-- to format and report multiple records of single student in one row instead of multiple rows
select
src.id
, src.sname
, max(src.Math_score) as Maths
, max(Science_score) as Science
, max(Social_score) as Scocial
from(
-- Subquery to pivot the rows to columns using case statement
select
id, sname,
case when subject = 'Maths' then score end as Math_score,
case when subject = 'Science' then score end as Science_score,
case when subject = 'Social' then score end as Social_score
from mydbname.stscorecard
) as src
group by id, sname
order by id, sname
;
--NOTE: Here we should know the exact numbner of subjects we expect to see at the max in source data.
-- In this example 3 is the maximum number of sucjects that students can get scores.
-- So we do have 3 case statements to pivot rows to columns
-- Subquery result set prior to applying aggragtion to combine multiple rows to singe row
1,student1,90,null,null
1,student1,null,91,null
1,student1,null,null,99
1,student2,92,null,null
1,student2,null,93,null
1,student2,null,null,94
-- Final result
1,student1,90,91,99
1,student2,92,93,94
Comments
Post a Comment