Pivoting a Table

1061172 · Dec 19, 2013 12:03AM

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

1061172 · Dec 19, 2013 1:04PM

My output should be something like this:

select studentdetails.applicationnumber

,min(DECODE(SUBJECT,'MATHEMATICS', 1)) as "MATHEMATICS"

,min(DECODE(SUBJECT,'ENGLISH', 1)) as "ENGLISH"

,min(DECODE(SUBJECT,'BIOLOGY', 1)) as "BIOLOGY"

,min(DECODE(SUBJECT,'SCIENCE', 1)) as "SCIENCE"

,min(DECODE(SUBJECT,'COMMERCE', 1)) as "COMMERCE"

,min(DECODE(SUBJECT,'ZAMBIAN LANGUAGE', 1)) as "ZAMBIAN LANGUAGE"

,min(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

with its student details which are: title, firstname, middlename,lastname, date of birth, age,gender, marrital status, nrc number and registered by

Frank Kulash · Dec 19, 2013 11:15AM

Hi,

Instead on COUNT, you probably wnat MIN or MAX. (If the combination appplicationnuimber + subject is unique, it doesn't matter which.)

Have you seen the forum FAQ page on pivoting? https://forums.oracle.com/message/9362005#9362005

I hope this answers your question.

If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

Explain, using specific examples, how you get those results from that data.

Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

See the forum FAQ: https://forums.oracle.com/message/9362002

Partha Sarathy S · Dec 19, 2013 12:51PM

Can you post your required output?

1061172 · Dec 19, 2013 1:04PM

My output should be something like this:

select studentdetails.applicationnumber

,min(DECODE(SUBJECT,'MATHEMATICS', 1)) as "MATHEMATICS"

,min(DECODE(SUBJECT,'ENGLISH', 1)) as "ENGLISH"

,min(DECODE(SUBJECT,'BIOLOGY', 1)) as "BIOLOGY"

,min(DECODE(SUBJECT,'SCIENCE', 1)) as "SCIENCE"

,min(DECODE(SUBJECT,'COMMERCE', 1)) as "COMMERCE"

,min(DECODE(SUBJECT,'ZAMBIAN LANGUAGE', 1)) as "ZAMBIAN LANGUAGE"

,min(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

with its student details which are: title, firstname, middlename,lastname, date of birth, age,gender, marrital status, nrc number and registered by

1061172 · Dec 19, 2013 1:17PM

output should be:

APPLICATIONNUMBER

TITLE

FIRSTNAME

MIDDLENAME

LASTNAME

DATEOFBIRTH

AGE

GENDER

MARRITAL_STATUS

NRC_NUMBER

REGISTERED_BY

MATHEMATICS

ENGLISH

BIOLOGY

SCIENCE

COMMERCE

ZAMBIAN LANGUAGE

ART

201300001

Mr

Lawrence

Mukombo

12/11/1980

33

Male

Married

219879/24/1

SYSTEM

2

1

4

3

4

5

1

Frank Kulash · Dec 19, 2013 3:03PM

Hi,

Thanks for posting the CREATE TABLE and INSERT statements for the studentdetails table.

If the other tables play any role in this problem, then you need to post CREATE TABLE and INSERT statements for them, too. (If you want to show scores in the pivoted columns, then the student_scores table probably is important. I'm less sure about the subjects table.)

Don't forget to post the exact results you want from whatever sample data you've posted.

Simplify the problem. Instead of showing 7 subjects, for example, show maybe 3. We'll find a solution that works for either 3 or 7.

The 3rd argument to DECODE is what you want to return.

,min(DECODE(SUBJECT,'MATHEMATICS', 1)) as "MATHEMATICS"

Do you really want to return the number 1 every time, or do you want to some column, such as final_score? If the latter, use

,  min (DECODE (SUBJECT, 'MATHEMATICS', FINAL_SCORE))   as MATHEMATICS

"

Partha Sarathy S · Dec 19, 2013 8:50PM

Please provide proper CREATE and INSERT scripts. There are lot of errors in the script you have provided.

1) CREATE table scripts have alter table command to add foreign key, but the CREATE statement for tables used in integrity are not provided

2) In the INSERT script, "dateofbirth" column is mentioned as date of birth, "marrital_status" is mentioned as marrital status.

3) In the INSERT, there are 11 columns in the columns list, but there are only 10 values in the values list.

4) STUD_DET_ID column is PRIMARY KEY, but you are not inserting value into it.(getting CANNOT INSERT NULL INTO studentdetails.STUD_DET_ID)

5) If you are inserting into a date column, use TO_DATE function to insert proper date format into the column.

6) Where is the CREATE and INSERT script for SUBJECTS and STUDENT_SCORES table?

1061172 · Dec 20, 2013 12:19AM

Hi Sarathy, sorry for that. How about this?

CREATE TABLE  "STUDENTDETAILS" 
   (     "STUD_DET_ID" NUMBER NOT NULL ENABLE, 
     "TITLE" VARCHAR2(6), 
     "FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE, 
     "MIDDLENAME" VARCHAR2(20), 
     "LASTNAME" VARCHAR2(20), 
     "DATEOFBIRTH" DATE, 
     "AGE" NUMBER, 
     "GENDER" VARCHAR2(10), 
     "MARRITAL_STATUS" VARCHAR2(10), 
     "NRC_NUMBER" VARCHAR2(11), 
     "REGISTERED_BY" VARCHAR2(30), 
     "ADDRESS_ID" NUMBER, 
     "SUBJECT_ID" NUMBER, 
     "STUD_SCORE_ID" NUMBER, 
     "EMPLOYEE_ID" NUMBER, 
     "APPLICATIONNUMBER" NUMBER NOT NULL ENABLE, 
      CONSTRAINT "STUDENTDETAILS_PK" PRIMARY KEY ("STUD_DET_ID") ENABLE
   ) ;ALTER TABLE  "STUDENTDETAILS" ADD CONSTRAINT "STUDENTDETAILS_CON" FOREIGN KEY ("ADDRESS_ID")
       REFERENCES  "ADDRESS" ("ADDRESS_ID") ENABLE;ALTER TABLE  "STUDENTDETAILS" ADD CONSTRAINT "STUDENTDETAILS_EMP_CON" FOREIGN KEY ("EMPLOYEE_ID")
       REFERENCES  "EMPLOYEES" ("EMPLOYEE_ID") ENABLE;ALTER TABLE  "STUDENTDETAILS" ADD CONSTRAINT "STUDENTDETAILS_SCORE_CON" FOREIGN KEY ("STUD_SCORE_ID")
       REFERENCES  "STUDENT_SCORES" ("STUD_SCORE_ID") ENABLE;

CREATE OR REPLACE TRIGGER  "BI_STUDENTDETAILS" 
  before insert on "STUDENTDETAILS"               
  for each row  
begin   
  if :NEW."STUD_DET_ID" is null then 
    select "STUDENTDETAILS_SEQ".nextval into :NEW."STUD_DET_ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_STUDENTDETAILS" ENABLE;

CREATE OR REPLACE TRIGGER  "STUDENTDETAILS_T1" 
BEFORE
insert or update or delete on "STUDENTDETAILS"
for each row
begin
if :new."REGISTERED_BY" is null then
    :new."REGISTERED_BY" := nvl(v('APP_USER'),USER);
    end if;
end;



/
ALTER TRIGGER  "STUDENTDETAILS_T1" ENABLE;

CREATE TABLE  "SUBJECTS" 
   (     "SUBJECT_ID" NUMBER NOT NULL ENABLE, 
     "SUBJECT_CODE" VARCHAR2(10), 
     "SUBJECT" VARCHAR2(25), 
      CONSTRAINT "SUBJECTS_PK" PRIMARY KEY ("SUBJECT_ID") ENABLE
   ) ;

CREATE OR REPLACE TRIGGER  "BI_SUBJECTS" 
  before insert on "SUBJECTS"               
  for each row  
begin   
  if :NEW."SUBJECT_ID" is null then 
    select "SUBJECTS_SEQ".nextval into :NEW."SUBJECT_ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_SUBJECTS" ENABLE;

CREATE TABLE  "STUDENT_SCORES" 
   (     "STUD_SCORE_ID" NUMBER NOT NULL ENABLE, 
     "SUBJECT_ID" NUMBER, 
     "SUBJECT_SCORE" NUMBER, 
     "STUD_DET_ID" NUMBER, 
     "APPLICATIONNUMBER" NUMBER NOT NULL ENABLE, 
      CONSTRAINT "STUDENT_SCORES_PK" PRIMARY KEY ("STUD_SCORE_ID") ENABLE
   ) ;ALTER TABLE  "STUDENT_SCORES" ADD CONSTRAINT "STUDENT_SCORES_CON" FOREIGN KEY ("SUBJECT_ID")
       REFERENCES  "SUBJECTS" ("SUBJECT_ID") ENABLE;

CREATE OR REPLACE TRIGGER  "BI_STUDENT_SCORES" 
  before insert on "STUDENT_SCORES"               
  for each row  
begin   
  if :NEW."STUD_SCORE_ID" is null then 
    select "STUDENT_SCORES_SEQ".nextval into :NEW."STUD_SCORE_ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_STUDENT_SCORES" ENABLE;

CREATE TABLE  "DISTRICTS" 
   (     "DISTRICT_ID" NUMBER NOT NULL ENABLE, 
     "PROVINCE_ID" NUMBER, 
     "DISTRICT_CODE" VARCHAR2(6), 
     "DISTRICT" VARCHAR2(20), 
      CONSTRAINT "DISTRICTS_PK" PRIMARY KEY ("DISTRICT_ID") ENABLE
   ) ;ALTER TABLE  "DISTRICTS" ADD CONSTRAINT "DISTRICTS_CON" FOREIGN KEY ("PROVINCE_ID")
       REFERENCES  "PROVINCES" ("PROVINCE_ID") ENABLE;

CREATE OR REPLACE TRIGGER  "BI_DISTRICTS" 
  before insert on "DISTRICTS"               
  for each row  
begin   
  if :NEW."DISTRICT_ID" is null then 
    select "DISTRICTS_SEQ".nextval into :NEW."DISTRICT_ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_DISTRICTS" ENABLE;

CREATE TABLE  "PROVINCES" 
   (     "PROVINCE_ID" NUMBER NOT NULL ENABLE, 
     "PROVINCE_CODE" VARCHAR2(30), 
     "PROVINCE" VARCHAR2(35), 
      CONSTRAINT "PROVINCES_PK" PRIMARY KEY ("PROVINCE_ID") ENABLE
   ) ;

CREATE OR REPLACE TRIGGER  "BI_PROVINCES" 
  before insert on "PROVINCES"               
  for each row  
begin   
  if :NEW."PROVINCE_ID" is null then 
    select "PROVINCES_SEQ".nextval into :NEW."PROVINCE_ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_PROVINCES" ENABLE;

CREATE TABLE  "EMPLOYEES" 
   (     "EMPLOYEE_ID" NUMBER NOT NULL ENABLE, 
     "FIRSTNAME" VARCHAR2(20), 
     "MIDDLENAME" VARCHAR2(20), 
     "LASTNAME" VARCHAR2(20), 
     "DESIGNATION" VARCHAR2(35), 
     "NRC_NUMBER" VARCHAR2(15), 
     "EMPLOYEE_NUMBER" NUMBER, 
      CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE
   ) ;

CREATE OR REPLACE TRIGGER  "BI_EMPLOYEES" 
  before insert on "EMPLOYEES"               
  for each row  
begin   
  if :NEW."EMPLOYEE_ID" is null then 
    select "EMPLOYEES_SEQ".nextval into :NEW."EMPLOYEE_ID" from dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_EMPLOYEES" ENABLE;

I have now included ALL the tables in the database, the The Subjects Table stores All the subjects, Student Scores stores the score for each subject a student registers, the District Table stores all the district in my country, the provinces table stores all Provinces in my country, the latter 2 will be used in the address for the district and province fields (Data Integrity), the Employees was supposed to store all the staff at the institution, the field "registered_by" in the studentdetails was to come from here.

and the corrected Insert script:

INSERT INTO studentdetails (stud_det_id, applicationnumber, title, firstname, middlename,lastname, dateofbirth, age,gender, marritalstatus, nrc_number,registered_by)

VALUES (1,201300001, 'Lawrence', 'Chiwape',

'Mukombo', '11-12-1988', NULL, 'Male', 'Married', '219856/47/1',NULL);

INSERT INTO studentdetails (stud_det_id, applicationnumber, title, firstname, middlename,lastname, dateofbirth, age,gender, marritalstatus, nrc_number,registered_by)

VALUES (2,201300002, 'Grace', 'Bwalya',

'Mutale', '18-09-1984', NULL, 'Female', 'Married', '782463/24/1',NULL);

INSERT INTO studentdetails (stud_det_id, applicationnumber, title, firstname, middlename,lastname, dateofbirth, age,gender, marritalstatus, nrc_number,registered_by)

VALUES (3,201300003, 'Gregory', NULL,

'Chilufya', '01-01-1980', NULL, 'Male', 'Married', '578963/11/1',NULL);

INERT INTO SUBJECTS (subject_id, subject_code, Subject)

VALUES (1,Maths, Mathematics)

INERT INTO SUBJECTS (subject_id, subject_code, Subject)

VALUES (2,Eng, English)

INERT INTO SUBJECTS (subject_id, subject_code, Subject)

VALUES (2,Scie, Science)

INERT INTO SUBJECTS (subject_id, subject_code, Subject)

VALUES (3,Bio, Biology)

INERT INTO SUBJECTS (subject_id, subject_code, Subject)

VALUES (4,Com, Commerce)

INERT INTO SUBJECTS (subject_id, subject_code, Subject)

VALUES (5,Zam Lan, Zambian Language)