Results 1 to 3 of 3

Thread: sql query

  1. #1
    Join Date
    Nov 2008

    Question Unanswered: sql query


    i m having a query .

    select a.rollno , as boy1, as boy2
    cmpt a,mbt b,cbt c
    a.rollno=b.rollno and

    it is displaying me the result like
    rollno boy1 boy2
    121 ajay ram

    i want to display it in two different rows ?
    rollno boy1 boy2
    121 ajay null

    121 null ram

    how shud i do it??????
    plzzz help

  2. #2
    Join Date
    Mar 2007
    how shud i do it??????
    plzzz help
    Please keep this forum in English.

    To the problem: firstly I thought about creating two subqueries joining tables (A, B) and (A, C) and then get the final resultset using UNION ALL, something like
    SELECT a.rollno , AS boy1, NULL AS boy2
    FROM cmpt a, mbt b
    WHERE a.rollno=b.rollno
    SELECT a.rollno, NULL AS boy1, AS boy2
    FROM cmpt a, cbt c
    WHERE a.rollno=c.rollno
    But, this approach may return extra rows when there is no corresponding ROLLNO in one of (B, C) tables. You may add checks on existence of the ROLLNO in the opposite table (EXISTS), but this would not be very performant.

    Another possibility is unpivotting the current query.The method for11g is described e.g. on this Oracle page:

  3. #3
    Join Date
    Dec 2008

    Thumbs down look at

    You can check the rollup and cube options to make your output as you wished instead of making a long sql query.

Posting Permissions

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