8 Replies Latest reply: May 18, 2013 12:02 PM by jeneesh RSS

    form sql query output

    coco
      Hi,

      Is it any modality to format the result as following without using plsql?

      The query:
      select d.deptid, e.ename
               from dept d 
               join emp e on d.deptid = e.deptid;
      the output
      1, test
      1, test1
      1, test2
      2, test3
      2, test4 
      ..
      and I need the output as following
      1, test
      null, test1
      null, test2
      2, test3
      null, test4
      ...
      Thank you
        • 1. Re: form sql query output
          jeneesh
          If you are using SQLPlus, you can use BREAK
          break on deptid
          Or
          select decode(row_number() over(parttion by d.deptid order by e.ename),
                1,d.deptid,null) dept_id, e.ename
          from dept d 
            join emp e 
              on d.deptid = e.deptid
          order by d.deptid,e.ename;
          Edited by: jeneesh on May 18, 2013 10:04 PM
          • 2. Re: form sql query output
            Frank Kulash
            Hi,
            coco wrote:
            Hi,

            Is it any modality to format the result as following without using plsql?

            The query:
            select d.deptid, e.ename
            from dept d 
            join emp e on d.deptid = e.deptid;
            the output
            1, test
            1, test1
            1, test2
            2, test3
            2, test4 
            ..
            and I need the output as following
            1, test
            null, test1
            null, test2
            2, test3
            null, test4
            ...
            Thank you
            So you don't want to display deptid if it'sthe same as deptid on the previous row, is that it?

            If you really need to have a NULL in the result set, you can use analytic functions, such as ROW_NUMBER;
            SELECT       CASE
                       WHEN  ROW_NUMBER () OVER ( PARTITION BY  d.dname
                                                              ORDER BY      e.ename
                                       ) = 1
                       THEN  d.dname
                   END          AS dname
            ,       e.ename
            FROM       scott.dept d
            JOIN       scott.emp  e     ON     d.deptno = e.deptno
            ORDER BY  d.dname
            ,            e.ename
            ;
            If you just need to make it look like the first column is NULL, then yor front end probably has some easier way. In SQL*Plus, for example, there's BREAK:
            BREAK     ON dname
            
            
            SELECT       d.dname
            ,       e.ename
            FROM       scott.dept d
            JOIN       scott.emp  e     ON     d.deptno = e.deptno
            ORDER BY  d.dname
            ,            e.ename
            ;
            Either way, the output looks the same:
            DNAME          ENAME
            -------------- ----------
            ACCOUNTING     CLARK
                           KING
                           MILLER
            RESEARCH       ADAMS
                           FORD
                           JONES
                           SCOTT
                           SMITH
            SALES          ALLEN
                           BLAKE
                           JAMES
                           MARTIN
                           TURNER
                           WARD
            Edited by: Frank Kulash on May 18, 2013 12:47 PM
            • 3. Re: form sql query output
              coco
              Yes I do not the deptid if it's the same as the previous row.

              Thank you
              • 4. Re: form sql query output
                jeneesh
                coco wrote:
                Yes I do not the deptid if it's the same as the previous row.

                Thank you
                Did you try the methods already provided in the first post?

                BREAK or DECODE(row_number() ...)
                • 5. Re: form sql query output
                  coco
                  I tried with the decode, but I cannot figure why I get "missing right parenthesis" error
                  • 6. Re: form sql query output
                    Frank Kulash
                    Hi,
                    coco wrote:
                    Yes I do not the deptid if it's the same as the previous row.
                    As Jeneesh said, what's wrong with the solutions already posted?

                    If you can't use commonly available tables (such as those in the scott schema) then post CREATE TABLE and INSERT statements for your own tables.
                    Whatever sample data you use, post the exact results you want from that data.
                    Point out where the solutions aove are not doing what you want, and explain how you get the correct results in those places.
                    coco wrote:
                    I tried with the decode, but I cannot figure why I get "missing right parenthesis" error
                    PARTITION is mis-spelled in Jeneesh's solution. Try:
                    select decode(row_number() over(partition by d.deptid order by e.ename),
                          1,d.deptid,null) dept_id, e.ename
                    from dept d 
                      join emp e 
                        on d.deptid = e.deptid
                    order by d.deptid,e.ename;
                    Edited by: Frank Kulash on May 18, 2013 1:20 PM
                    • 7. Re: form sql query output
                      coco
                      The both solutions are correct.
                      In the solution posted by jeneesh appear pattition instead of partition. I just changed this and now both soloyions are working.

                      Thank you both of you, you are very professional guys and learned a lot.

                      Thank you
                      • 8. Re: form sql query output
                        EdStevens
                        coco wrote:
                        I tried with the decode, but I cannot figure why I get "missing right parenthesis" error
                        I'm going to take a long shot here and guess that you have a missing right parenthesis. Unfortunately, my web cam is broken so I can't see your code to spot where that missing parenthesis might be. Would you be so kind as to post your code, enclosed in the \
                         tags?