DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Query

  1. #1
    Join Date
    Aug 2001
    Posts
    134

    Thumbs down

    select ID_NO, COURSE from COURSES
    order by ID_NO

    ID_NO Course
    --------------
    1 F101
    1 D106
    2 S105
    2 C101
    3 C101
    3 A503
    5 C102

    I wanna see the result in below format through simple procedure, Help.

    ID_NO Courses
    -----------------
    1 F101 D106
    2 S105 C101
    3 C101 A503
    5 C102

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    You can not do this with a sql query. You have to use PLSQL

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    go to asktom.oracle.com and search for pivot query

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by mike9
    You can not do this with a sql query. You have to use PLSQL
    Oh no no no, my good man. You'd be amazed what can be done in SQL.

    Try this:
    Code:
    CREATE TABLE CRL_STUDENT_COURSE (STUDENT_ID NUMBER, COURSE_CD CHAR(4));
    
    
    INSERT INTO CRL_STUDENT_COURSE VALUES(1, 'F101');
    INSERT INTO CRL_STUDENT_COURSE VALUES(1, 'D106');
    INSERT INTO CRL_STUDENT_COURSE VALUES(2, 'S105');
    INSERT INTO CRL_STUDENT_COURSE VALUES(2, 'C101');
    INSERT INTO CRL_STUDENT_COURSE VALUES(3, 'C101');
    INSERT INTO CRL_STUDENT_COURSE VALUES(3, 'A503');
    INSERT INTO CRL_STUDENT_COURSE VALUES(5, 'C102');
    
    SELECT
       T.STUDENT_ID,
       MAX(DECODE(T.RN, 1,       COURSE_CD)) ||
       MAX(DECODE(T.RN, 2,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN, 3,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN, 4,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN, 5,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN, 6,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN, 7,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN, 8,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN, 9,', '|| COURSE_CD)) ||
       MAX(DECODE(T.RN,10,', '|| COURSE_CD))
          AS COURSE_CD_LST   
    FROM
       (
       SELECT
          CSC.*   ,
          ROW_NUMBER()
             OVER(
                PARTITION BY 
                   CSC.STUDENT_ID
                ORDER BY
                   CSC.COURSE_CD
                )
             AS   RN
       FROM
          CRL_STUDENT_COURSE   CSC
       )   T
    GROUP BY
       T.STUDENT_ID
    The main problem is that the number of courses in the list is arbitrary. However, it really doesn't hurt much if you want to expand the code to handle 500 courses. It'll most likely still be faster than a PL/SQL solution.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    Depending on the Oracle release, maybe, actually you can do it in SQL using correlated subqueries to set up the crosstabs view, but this is in SQL-92 and SQL-99, which Oracle doesn't fully support.

    Example: Select idno,course,{select course from courses b where b.idno=a.idnot and b.course <> a.course} from courses a;

    or something like that.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  6. #6
    Join Date
    Aug 2001
    Posts
    134

    Thumbs down You can not do this with a sql query. You have to use PLSQL

    Thanx dear for quick reply.

    This is a solution of that problem in 9i.

    select
    ID_NO,
    sys.XMLTYPE.getCLOBval(
    sys.XMLTYPE.extract(a.xml,'/ROWSET/ROW/COURSE_LIST/text()') ) "Courses"
    from
    (
    select ID_NO, sys.XMLTYPE.createXML( xmlgen.getXML('select ID_NO,
    COURSE||'' '' COURSE_LIST from COURSES where ID_NO = '||ID_NO||' order
    by ID_NO') ) "XML" from COURSES group by ID_NO
    ) a

    But I wanna handle this with the simple procedure.

  7. #7
    Join Date
    Aug 2001
    Posts
    134
    F1
    F1
    F1

  8. #8
    Join Date
    Oct 2002
    Posts
    1

    Query

    Originally posted by jrpm
    Depending on the Oracle release, maybe, actually you can do it in SQL using correlated subqueries to set up the crosstabs view, but this is in SQL-92 and SQL-99, which Oracle doesn't fully support.

    Example: Select idno,course,{select course from courses b where b.idno=a.idnot and b.course <> a.course} from courses a;

    or something like that.

    I used PL/SQL 8.1.7 to run this query but the results are not as desired. Why?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width