1 Reply Latest reply: Nov 7, 2012 5:51 AM by fac586 RSS

    Is there any way to use Control Break in a SQL Query

    DeepakJ
      Hi,

      Is there any way to use a control break on Dept column in a SQL query to have a Output-2 instead of Output-1.
      Is there any way to modify the SQL query.

      SQL
      select dept, loc, count(*)
        from dept
      group by dept, loc
      Output-1
        Dept      Loc       Count(*)
        10         AA        1
        10         BB        2
        10         CC        2
        20         AA        2
        20         BB        2
      Output-2
        Dept      Loc       Count(*)
        10         AA        1
                   BB        2
                   CC        2
        20         AA        2
                   BB        2
      Thanks,
      Deepak
        • 1. Re: Is there any way to use Control Break in a SQL Query
          fac586
          DeepakJ wrote:
          Hi,

          Is there any way to use a control break on Dept column in a SQL query to have a Output-2 instead of Output-1.
          Is there any way to modify the SQL query.

          SQL
          select dept, loc, count(*)
          from dept
          group by dept, loc
          Output-1
          Dept      Loc       Count(*)
          10         AA        1
          10         BB        2
          10         CC        2
          20         AA        2
          20         BB        2
          Output-2
          Dept      Loc       Count(*)
          10         AA        1
          BB        2
          CC        2
          20         AA        2
          BB        2
          Yes, using the <tt>lag</tt> analytic function and specified ordering of the data:
          select
              nullif(d.deptno, lag(d.deptno) over (order by d.deptno, d.loc, e.mgr nulls first)) deptno
            , nullif(d.loc, lag(d.loc) over (order by d.deptno, d.loc, e.mgr nulls first)) loc
            , e.mgr
            , count(*) n
          from
              dept d
                join emp e
                  on d.deptno = e.deptno
          group by
              d.deptno
            , d.loc
            , e.mgr
          order by
              d.deptno
            , d.loc
            , e.mgr nulls first;
          
          DEPTNO  LOC       MGR   N
          ------  --------  ----  --
              10  NEW YORK         1 
                            7782   1 
                            7839   1 
              20  DALLAS    7566   2 
                            7788   1 
                            7839   1 
              30  CHICAGO   7698   4 
                            7839   1 
              40  BOSTON    7698   2 
                            7902   1