SQL Analytics 101 - Break columns

SQL analytics can be used to generate break columns in your queries, without the need for break formatting attributes in APEX or the old fashioned break on option in SQL*Plus.

I came across an example recently where I wanted to apply the break formatting in my query to avoid extra sub-totals from being displayed after each break.

No sub-totals please

I could use jQuery to hide the rows instead of modifying them, but as Tom Kyte says - if it can be done in SQL, why not? (or something like that...)

And it's less work for the database

select case when row_number() over (partition by d.dname order by d.dname, e.ename) = 1 then d.dname end dname
  ,e.ename, e.job, e.sal, e.comm
from dept d, emp e
where d.deptno = e.deptno
order by d.deptno, e.ename

The row_number() clause allocates a distinct row number for each set of departments (partition by clause).
The case statement only shows the department for the first row - and we need the order by clauses to match up to keep things neat.

A simple report demo is available here.

These are the results if the query was run in SQL Developer, with the row_number() clause also in it's own column.

RN         DNAME          ENAME      JOB              SAL       COMM
---------- -------------- ---------- --------- ---------- ----------
         1 ACCOUNTING     CLARK      MANAGER         2450            
         2                KING       PRESIDENT       5000            
         3                MILLER     CLERK           1300            
         1 RESEARCH       ADAMS      CLERK           1100            
         2                FORD       ANALYST         3000            
         3                JONES      MANAGER         2975            
         4                SCOTT      ANALYST         3000            
         5                SMITH      CLERK            800            
         1 SALES          ALLEN      SALESMAN        1600        300 
         2                BLAKE      MANAGER         2850            
         3                JAMES      CLERK            950            
         4                MARTIN     SALESMAN        1250       1400 
         5                TURNER     SALESMAN        1500          0 
         6                WARD       SALESMAN        1250        500 

 14 rows selected 

SQL analytics are worth wrapping your head around - they can offer simple solutions to common problems.