Write a SQL Query to Denormalize the student data by converting rows into columns

 

 

  

 

CREATE TABLE STUDENTS(

ID number,

NAME varchar(10),

SUBJECT varchar(10),

SCORE number

);INSERT INTO STUDENTS VALUES('1','Ram','Maths','50');

INSERT INTO STUDENTS VALUES('1','Ram','Physics','65');

INSERT INTO STUDENTS VALUES('1','Ram','Chemistry','70');

INSERT INTO STUDENTS VALUES('2','Neena','Maths','88');

INSERT INTO STUDENTS VALUES('2','Neena','Physics','90');

INSERT INTO STUDENTS VALUES('2','Neena','Chemistry','65');

INSERT INTO STUDENTS VALUES('3','John','Maths','100');

INSERT INTO STUDENTS VALUES('3','John','Physics','45');

INSERT INTO STUDENTS VALUES('3','John','Chemistry','52');

commit;

 

SELECT

ID,

NAME,

CASE WHEN Subject = 'Maths' THEN Score ELSE null END AS Maths,

CASE WHEN Subject = 'Physics' THEN Score Else null END AS Physics,

CASE WHEN Subject = 'Chemistry' THEN Score Else null END AS Chemistry

FROM Students;

 

SELECT

a.ID,

a.NAME,

MAX(a.Maths) AS Maths,

MAX(a.Physics) AS Physics,

MAX(a.Chemistry) AS Chemistry

FROM(

SELECT

ID,

NAME,

CASE WHEN Subject = 'Maths' THEN Score ELSE null END AS Maths,

CASE WHEN Subject = 'Physics' THEN Score Else null END AS Physics,

CASE WHEN Subject = 'Chemistry' THEN Score Else null END AS Chemistry

FROM Students) a

GROUP BY ID, NAME;

 

SELECT * FROM Students

PIVOT (MAX(Score) FOR Subject IN ('Maths' AS Maths, 'Physics' AS Physics,'Chemistry' AS Chemistry))

ORDER BY ID;