Hi everyone, i am a new in this community, am trying to decode this statement which is giving me many rows for the same student on the different subjects.
select "STUDENTDETAILS"."APPLICATIONNUMBER" as "APPLICATIONNUMBER",
"STUDENTDETAILS"."TITLE" as "TITLE",
"STUDENTDETAILS"."FIRSTNAME" as "FIRSTNAME",
"STUDENTDETAILS"."MIDDLENAME" as "MIDDLENAME",
"STUDENTDETAILS"."LASTNAME" as "LASTNAME",
"STUDENTDETAILS"."DATEOFBIRTH" as "DATEOFBIRTH",
"STUDENTDETAILS"."AGE" as "AGE",
"STUDENTDETAILS"."GENDER" as "GENDER",
"STUDENTDETAILS"."MARRITAL_STATUS" as "MARRITAL_STATUS",
"STUDENTDETAILS"."NRC_NUMBER" as "NRC_NUMBER",
"STUDENTDETAILS"."REGISTERED_BY" as "REGISTERED_BY",
"SUBJECTS"."SUBJECT" as "SUBJECT",
"STUDENT_SCORES"."SUBJECT_SCORE" as "SUBJECT_SCORE"
from "SUBJECTS" "SUBJECTS",
"STUDENT_SCORES" "STUDENT_SCORES",
"STUDENTDETAILS" "STUDENTDETAILS"
where "STUDENTDETAILS"."APPLICATIONNUMBER"="STUDENT_SCORES"."APPLICATIONNUMBER"
and "STUDENT_SCORES"."SUBJECT_ID"="SUBJECTS"."SUBJECT_ID"
| APPLICATIONNUMBER | TITLE | FIRSTNAME | MIDDLENAME | LASTNAME | DATEOFBIRTH | AGE | GENDER | MARRITAL_STATUS | NRC_NUMBER | REGISTERED_BY | SUBJECT | SUBJECT_SCORE |
| 201300001 | Mr | Lawrence | | Mukombo | 12/11/1980 | 33 | Male | Married | 219879/24/1 | SYSTEM | MATHEMATICS | 2 |
| 201300001 | Mr | Lawrence | | Mukombo | 12/11/1980 | 33 | Male | Married | 219879/24/1 | SYSTEM | ENGLISH | 4 |
| 201300001 | Mr | Lawrence | | Mukombo | 12/11/1980 | 33 | Male | Married | 219879/24/1 | SYSTEM | BIOLOGY | 3 |
| 201300001 | Mr | Lawrence | | Mukombo | 12/11/1980 | 33 | Male | Married | 219879/24/1 | SYSTEM | COMMERCE | 5 |
| 201300001 | Mr | Lawrence | | Mukombo | 12/11/1980 | 33 | Male | Married | 219879/24/1 | SYSTEM | ZAMBIAN LANGUAGE | 3 |
| 201300001 | Mr | Lawrence | | Mukombo | 12/11/1980 | 33 | Male | Married | 219879/24/1 | SYSTEM | ART | 1 |
I have decoded this far by i don't know how i can join the personal details on this same student. When i use "count" am getting the number of rows and not the data under that heading.
How can i best create and join a pivot?
select studentdetails.applicationnumber
,count(DECODE(SUBJECT,'MATHEMATICS', 1)) as "MATHEMATICS"
,count(DECODE(SUBJECT,'ENGLISH', 1)) as "ENGLISH"
,count(DECODE(SUBJECT,'BIOLOGY', 1)) as "BIOLOGY"
,count(DECODE(SUBJECT,'SCIENCE', 1)) as "SCIENCE"
,count(DECODE(SUBJECT,'COMMERCE', 1)) as "COMMERCE"
,count(DECODE(SUBJECT,'ZAMBIAN LANGUAGE', 1)) as "ZAMBIAN LANGUAGE"
,count(DECODE(SUBJECT,'ART', 1)) as "ART"
from "SUBJECTS" "SUBJECTS",
"STUDENT_SCORES" "STUDENT_SCORES",
"STUDENTDETAILS" "STUDENTDETAILS"
where "STUDENTDETAILS"."APPLICATIONNUMBER"="STUDENT_SCORES"."APPLICATIONNUMBER"
and "STUDENT_SCORES"."SUBJECT_ID"="SUBJECTS"."SUBJECT_ID"
group by studentdetails.applicationnumber
order by studentdetails.applicationnumber
| APPLICATIONNUMBER | MATHEMATICS | ENGLISH | BIOLOGY | SCIENCE | COMMERCE | ZAMBIAN LANGUAGE | ART |
|---|
| 201300001 | 1 | 1 | 1 | 0 | 1 | 1 | 1 |