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!

Query to display value in column wise

Discussion in 'General' started by Premsagar, May 7, 2017.

  1. Premsagar

    Premsagar Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Chennai
    Hi All,
    I’m New to ORACLE , My requirement is to capture a list of values and to display values in Column Order, Eg :
    Table 1 : Student_info
    ID Lname Fname Age Sex
    1 Test1 Test11 20 M
    2 Test2 Test12 21 F
    3 Test3 Test13 22 M
    4 Test4 Test14 23 F
    5 Test5 Test15 24 M

    Table2: Student_Mark
    ID Sub Score
    1 M1 50
    1 M2 60
    1 M3 70
    1 M4 80
    2 M1 50
    2 M2 60
    2 M3 70
    2 M4 80
    3 M1 50
    3 M2 60
    3 M3 70
    3 M4 80
    4 M1 50
    4 M2 60
    4 M3 70
    4 M4 80
    5 M1 50
    5 M2 60
    5 M3 70
    5 M4 80

    Output :
    ID Lname Fname Age Sex SUB M1 SUB M2 SUB M3 SUM M4
    1 Test1 Test11 20 M 50 60 70 80
    2 Test2 Test12 21 F 50 60 70 80
    3 Test3 Test13 22 M 50 60 70 80
    4 Test4 Test14 23 F 50 60 70 80
    5 Test5 Test15 24 M 50 60 70 80

    Can some one share query to display o/p in above format .
    Thanks in advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you tried to get the desired results? Post your work and we will help you; we won't write it for you.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's a shame you won't reply to responses; this is an interesting problem with an interesting solution, although it's not exactly as you asked for it to be:

    Code (SQL):
    SQL> CREATE TABLE student_info(
      2  ID NUMBER,
      3  Lname      varchar2(30),
      4  Fname      varchar2(30),
      5  Age        NUMBER,
      6  Sex        varchar2(1));

    TABLE created.

    SQL>
    SQL> INSERT ALL
      2  INTO student_info
      3  VALUES(1, 'Test1', 'Test11', 20, 'M')
      4  INTO student_info
      5  VALUES(2, 'Test2', 'Test12', 21, 'F')
      6  INTO student_info
      7  VALUES(3, 'Test3', 'Test13', 22, 'M')
      8  INTO student_info
      9  VALUES(4, 'Test4', 'Test14', 23, 'F')
    10  INTO student_info
    11  VALUES(5, 'Test5', 'Test15', 24, 'M')
    12  SELECT * FROM dual;

    5 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> CREATE TABLE Student_Mark(
      2  ID NUMBER,
      3  Sub        varchar2(2),
      4  Score      NUMBER);

    TABLE created.

    SQL>
    SQL> INSERT ALL
      2  INTO student_mark
      3  VALUES(1, 'M1', 50)
      4  INTO student_mark
      5  VALUES(1, 'M2', 60)
      6  INTO student_mark
      7  VALUES(1, 'M3', 70)
      8  INTO student_mark
      9  VALUES(1, 'M4', 80)
    10  INTO student_mark
    11  VALUES(2, 'M1', 79)
    12  INTO student_mark
    13  VALUES(2, 'M2', 90)
    14  INTO student_mark
    15  VALUES(2, 'M3', 78)
    16  INTO student_mark
    17  VALUES(2, 'M4', 83)
    18  INTO student_mark
    19  VALUES(3, 'M1', 90)
    20  INTO student_mark
    21  VALUES(3, 'M2', 94)
    22  INTO student_mark
    23  VALUES(3, 'M3', 88)
    24  INTO student_mark
    25  VALUES(3, 'M4', 80)
    26  INTO student_mark
    27  VALUES(4, 'M1', 58)
    28  INTO student_mark
    29  VALUES(4, 'M2', 80)
    30  INTO student_mark
    31  VALUES(4, 'M3', 78)
    32  INTO student_mark
    33  VALUES(4, 'M4', 88)
    34  INTO student_mark
    35  VALUES(5, 'M1', 70)
    36  INTO student_mark
    37  VALUES(5, 'M2', 67)
    38  INTO student_mark
    39  VALUES(5, 'M3', 77)
    40  INTO student_mark
    41  VALUES(5, 'M4', 87)
    42  SELECT * FROM dual;

    20 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> COLUMN sublist new_value s_list noprint
    SQL>
    SQL> SELECT listagg(''''||sub||'''', ',') WITHIN GROUP (ORDER BY id) sublist
      2  FROM student_mark
      3  WHERE id = 1;




    SQL> -- connect by nocycle id = prior id
    SQL> -- group by level;
    SQL>
    SQL> SELECT *
      2  FROM
      3  (SELECT id, sub, score FROM student_mark) pivot (MAX(score) score FOR sub IN (&s_list));
    OLD   3: (SELECT id, sub, score FROM student_mark) pivot (MAX(score) score FOR sub IN (&s_list))
    NEW   3: (SELECT id, sub, score FROM student_mark) pivot (MAX(score) score FOR sub IN ('M1','M2','M3','M4'))

    'M1'_SCORE 'M2'_SCORE 'M3'_SCORE 'M4'_SCORE
    ---------- ---------- ---------- ----------
            50         60         70         80
            79         90         78         83
            58         80         78         88
            70         67         77         87
            90         94         88         80

    SQL>
    SQL> COLUMN id noprint
    SQL>
    SQL> SELECT s.id stud_id, s.lname, s.fname, s.age, s.sex, m.*
      2  FROM student_info s, (SELECT * FROM (SELECT id, sub, score FROM student_mark) pivot (MAX(score) score FOR sub IN (&s_list))) m
      3  WHERE m.id = s.id;
    OLD   2: FROM student_info s, (SELECT * FROM (SELECT id, sub, score FROM student_mark) pivot (MAX(score) score FOR sub IN (&s_list))) m
    NEW   2: FROM student_info s, (SELECT * FROM (SELECT id, sub, score FROM student_mark) pivot (MAX(score) score FOR sub IN ('M1','M2','M3','M4'))) m

       STUD_ID LNAME                          FNAME                                 AGE S 'M1'_SCORE 'M2'_SCORE 'M3'_SCORE 'M4'_SCORE
    ---------- ------------------------------ ------------------------------ ---------- - ---------- ---------- ---------- ----------
             1 Test1                          Test11                                 20 M         50         60     70             80
             2 Test2                          Test12                                 21 F         79         90     78             83
             4 Test4                          Test14                                 23 F         58         80     78             88
             5 Test5                          Test15                                 24 M         70         67     77             87
             3 Test3                          Test13                                 22 M         90         94     88             80

    SQL>
    Using the pivot() operator, the listagg() function and generating a variable to hold the generated list the data is displayed. With this solution if new subjects are added then they will automatically be included in the pivot() list.