1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Denormalizing using query.,!

Discussion in 'SQL PL/SQL' started by Vicky, Dec 29, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    I've a table like this.,

    Code (SQL):


    STU_NAME  SUBJECT   MARKS
    --------- --------- ------
    1         ENGLISH   90  
    1         TAMIL     80  
    1         MATHS     70  
    2         MATHS     70  
    2         TAMIL     80  
    2         ENGLISH   95  

     
    And the result should be like below.,

    Code (SQL):


    STU_NAME  MATHS_MARK  ENGLISH_MARK TAMIL_MARK  TOTAL_MARKS
    --------- ----------- ------------ ----------- -------------
    1            70            90            80            240
    2            70            95            80            245      
     
     
    Cud v achieve the same using query?
     
    Last edited: Dec 29, 2015
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, you can.
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I got the same using PIVOT function.,

    Code (SQL):

    SELECT*FROM(SELECT*FROM x)PIVOT(MAX(marks)FOR(SUBJECT)IN('ENGLISH','MATHS','TAMIL'));
     
    But, Still I'm facing few issues.,
    1. whether it is possible to get sum(marks) while using PIVOT.
    2. And getting subjects with respective marks without mentioning every subject(when we do know what are the subjects will be) in both(case,pivot).,?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I believe this is an example for an older method, DECODE:

    Code (SQL):
    SQL> CREATE TABLE student_grades(
      2  stu_name        varchar2(20),
      3  subject         varchar2(20),
      4  marks           NUMBER);

    TABLE created.

    SQL>
    SQL> INSERT ALL
      2  INTO student_grades
      3  VALUES('1','ENGLISH',  90)
      4  INTO student_grades
      5  VALUES('1','TAMIL',  80)
      6  INTO student_grades
      7  VALUES('1','MATHS',  70)
      8  INTO student_grades
      9  VALUES('2','MATHS',  70)
    10  INTO student_grades
    11  VALUES('2','TAMIL',  80)
    12  INTO student_grades
    13  VALUES('2','ENGLISH',  95)
    14  SELECT * FROM dual;

    6 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SET echo ON linesize 200
    SQL>
    SQL> break ON report
    SQL> compute SUM label 'TOTAL' OF deptno_10 ON report
    SQL> compute SUM OF deptno_20 ON report
    SQL> compute SUM OF deptno_30 ON report
    SQL> compute SUM OF total ON report
    SQL> SELECT      stu_name
      2  , MAX (decode (subject, 'MATHS', marks, 0)) maths_marks
      3  , MAX (decode (subject, 'ENGLISH', marks, 0)) english_marks
      4  , MAX (decode (subject, 'TAMIL', marks, 0)) tamil_marks
      5  , MAX (decode (subject, 'ENGLISH', marks, 0))
      6  + MAX (decode (subject, 'TAMIL', marks, 0))
      7  + MAX (decode (subject, 'MATHS', marks, 0)) total_marks
      8  FROM        (SELECT        stu_name, subject, marks
      9  FROM        student_grades)
    10  GROUP BY stu_name
    11  /

    STU_NAME             MATHS_MARKS ENGLISH_MARKS TAMIL_MARKS TOTAL_MARKS
    -------------------- ----------- ------------- ----------- -----------
    1                             70            90          80         240
    2                             70            95          80         245

    SQL>
     
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes David., But it also works with SUM(marks) also,

    Code (SQL):

    SELECT      stu_name
    , MAX (decode (subject, 'MATHS', marks, 0)) maths_marks
    , MAX (decode (subject, 'ENGLISH', marks, 0)) english_marks
    , MAX (DECODE (SUBJECT, 'TAMIL', MARKS, 0)) TAMIL_MARKS
    , SUM(marks) TOTAL_MARKS
    FROM        STUDENT_DENORM
    GROUP BY stu_name
    ;
    /

    STU_NAME MATHS_MARKS ENGLISH_MARKS TAMIL_MARKS TOTAL_MARKS
    -------- ----------- ------------- ----------- -----------
    1  70  90  80  240  
    2  70  95  80  245  


     
    But, how to achieve the same, when we don't know what the subjects will be/.?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO