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!

Help in select statement

Discussion in 'SQL PL/SQL' started by waheeb, Sep 4, 2016.

  1. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    i have the study_plan table with columns
    (year_id,term_id,school_id,stage_id,class_id,course_id)
    How can I show every course individually in a column and every field gives the number of courses in that class.
    For example,
    class one has 4 courses ( course1,course2,course3,course4 ) , and the rest of the courses should be NULL .
    They result should appear like this:

    class_id course1 course2 course3 course4 course5 course6 coure7 cours8
    1 2 2 2 2
    2 2 2 2 2 2
    3 2 2 2 2 2 2


    Thanks.
     
  2. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    hi again, in case no one understood, maybe the PDF file clear.
     

    Attached Files:

    • A.pdf
      File size:
      83.5 KB
      Views:
      6
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Everything is very simple... it is necessary to use pivot operator.
    Additional link: pivot and unpivot
     
    waheeb likes this.
  4. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen


    select
    class_id,
    sum(decode(course_id,1,1)) "Quran",
    sum(decode(course_id,2,1)) "Islamic",
    sum(decode(course_id,3,1)) "Arabic",
    sum(decode(course_id,4,1)) "Math",
    sum(decode(course_id,9,1)) "English"
    from
    study_plan
    group by
    class_id
    order by 1

    this one up works and this one down works as well.
    I'm grateful to your unlimited support.Thank you.

    select * from
    (select class_id,course_id from study_plan)
    pivot
    (
    count(course_id)
    for course_id in (1 as "quran",2 as "Islamic",3 as "Arabic",4 as "Math",9 as "English")
    )
    order by 1
     
    Last edited: Sep 6, 2016
  5. zargon

    zargon Community Moderator Forum Guru

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

    Code (SQL):
    SQL> SELECT class_id, listagg(course_id, ' ') WITHIN GROUP (ORDER BY class_id) courses
      2  FROM study_plan
      3  GROUP BY class_id;

      CLASS_ID COURSES
    ---------- ----------------------------------------
             1 2 2 2 2
             2 2 2 2 2 2 2
             3 2 2 2 2 2 2 2

    SQL>