Using decode function to implement row variable column
----Create a test table
CREATE TABLE Student_score (
Name VARCHAR2 (20),
Subject VARCHAR2 (20),
Score Number (4,1)
);
-----Inserting test data
Insert into Student_score (Name,subject,score) VALUES (' Zhang San ', ' language ', 78);
Insert into Student_score (Name,subject,score) VALUES (' Zhang San ', ' math ', 88);
Insert into Student_score (Name,subject,score) VALUES (' Zhang San ', ' English ', 98);
Insert into Student_score (Name,subject,score) VALUES (' John Doe ', ' language ', 89);
Insert into Student_score (Name,subject,score) VALUES (' John Doe ', ' math ', 76);
Insert into Student_score (Name,subject,score) VALUES (' John Doe ', ' English ', 90);
Insert into Student_score (Name,subject,score) VALUES (' Harry ', ' Language ', 99);
Insert into Student_score (Name,subject,score) VALUES (' Harry ', ' math ', 66);
Insert into Student_score (Name,subject,score) VALUES (' Harry ', ' English ', 91);
-----Decode row to column
Select name "Name",
SUM (decode (subject, ' Language ', NVL (score, 0), 0)) "Language",
SUM (decode (subject, ' Mathematics ', NVL (score, 0), 0)) "Mathematics",
SUM (decode (subject, ' English ', NVL (score, 0), 0)) "English"
From Student_score
Group by name;
------case, row to column
Select name "Name",
SUM (case when subject= ' language '
Then NVL (score,0)
else 0
End) "Language",
SUM (case when subject= ' mathematics '
Then NVL (score,0)
else 0
End) "Math",
SUM (case when subject= ' English '
Then NVL (score,0)
else 0
End) "English"
From Student_score
Group by name;
Using decode function to implement row variable column