Forum Stats

  • 3,854,952 Users
  • 2,264,439 Discussions
  • 7,905,846 Comments

Discussions

Pivoting a Table

1061172
1061172 Member Posts: 12
edited Dec 20, 2013 12:39AM in SQL & PL/SQL

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"

APPLICATIONNUMBERTITLEFIRSTNAMEMIDDLENAMELASTNAMEDATEOFBIRTHAGEGENDERMARRITAL_STATUSNRC_NUMBERREGISTERED_BYSUBJECTSUBJECT_SCORE
201300001MrLawrenceMukombo12/11/198033MaleMarried219879/24/1SYSTEMMATHEMATICS2
201300001MrLawrenceMukombo12/11/198033MaleMarried219879/24/1SYSTEMENGLISH4
201300001MrLawrenceMukombo12/11/198033MaleMarried219879/24/1SYSTEMBIOLOGY3
201300001MrLawrenceMukombo12/11/198033MaleMarried219879/24/1SYSTEMCOMMERCE5
201300001MrLawrenceMukombo12/11/198033MaleMarried219879/24/1SYSTEMZAMBIAN LANGUAGE3
201300001MrLawrenceMukombo12/11/198033MaleMarried219879/24/1SYSTEMART1

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

APPLICATIONNUMBERMATHEMATICSENGLISHBIOLOGYSCIENCECOMMERCEZAMBIAN LANGUAGEART
2013000011110111
Tagged:
1061172

Best Answer

  • 1061172
    1061172 Member Posts: 12
    Answer ✓

    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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond

    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
    Partha Sarathy S Member Posts: 1,200 Silver Trophy

    Can you post your required output?

  • 1061172
    1061172 Member Posts: 12
    Answer ✓

    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
    1061172 Member Posts: 12

    output should be:

    APPLICATIONNUMBERTITLEFIRSTNAMEMIDDLENAMELASTNAMEDATEOFBIRTHAGEGENDERMARRITAL_STATUSNRC_NUMBERREGISTERED_BYMATHEMATICSENGLISHBIOLOGYSCIENCECOMMERCEZAMBIAN LANGUAGEART
    201300001MrLawrence Mukombo12/11/198033MaleMarried219879/24/1SYSTEM2143451
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Dec 19, 2013 3:05PM


    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

    "

    1061172Frank Kulash
  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200 Silver Trophy
    edited Dec 19, 2013 9:09PM

    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
    1061172 Member Posts: 12

    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)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (1,1,2,1,201300001)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (2,2,5,1,201300001)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (3,4,1,1,201300001)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (4,3,2,1,201300001)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (5,1,3,2,201300002)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (6,2,1,2,201300002)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (7,3,1,2,201300002)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (8,4,2,2,201300002)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (9,1,2,3,201300003)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (10,2,2,3,201300003)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (11,4,2,3,201300003)


    INSERT INTO STUDENT_SCORES (stud_score_id, subject_id,subject_score,stud_det_id,applicationnumber)

    VALUES (12,5,2,3,201300003)



    INSERT INTO DISTRICTS (district_id, province_id,district_code,district)

    VALUES (1,1,Sol,Solwezi)

    INSERT INTO DISTRICTS (district_id, province_id,district_code,district)

    VALUES (2,1,Kas,Kasempa)

    INSERT INTO DISTRICTS (district_id, province_id,district_code,district)

    VALUES (3,2,Mon,Mongu)

    INSERT INTO DISTRICTS (district_id, province_id,district_code,district)

    VALUES (4,3,Lun,Lundazi)

    INSERT INTO DISTRICTS (district_id, province_id,district_code,district)

    VALUES (5,7,Chon,Chongwe)

    INSERT INTO DISTRICTS (district_id, province_id,district_code,district)

    VALUES (6,7,Kaf,Kafue)


    INSERT INTO PROVINCES (province_id,province_code,province)

    VALUES (1,nw,northwestern)

    INSERT INTO PROVINCES (province_id,province_code,province)

    VALUES (2,westwestern)

    INSERT INTO PROVINCES (province_id,province_code,province)

    VALUES (3,east,eastern)

    INSERT INTO PROVINCES (province_id,province_code,province)

    VALUES (7,lsk,lusaka)

    for now i can give this, fell free to add more data if you wish




  • 1061172
    1061172 Member Posts: 12

    i would like the employee table to populate data for registered_by field in student details table

  • 1061172
    1061172 Member Posts: 12

    thanks Frank, Perfect, the latter is what i want and it has worked! my remaining problem is now is getting the personal details to join them to the decode script. The subjects are many and new subjects may be added, so i want the code to be flexible enough to capture all subjects with data in them.

    Thanks again for your help.

This discussion has been closed.