Oracle 11.2.0.1
Windows XP
I have to create a table (which data further have to exported to the excel file by SQL Developer) something like this :
User Scott Table Emp
create table temptbl as
SELECT
case when lead(deptno) OVER (ORDER BY deptno) = deptno then empno else null end AS empno,
case when lead(deptno) OVER (ORDER BY deptno) = deptno then ename else null end AS ename,
case when lead(deptno) OVER (ORDER BY deptno) = deptno then job else null end AS job,
case when lead(deptno) OVER (ORDER BY deptno) = deptno then deptno else null end AS deptno
FROM emp
Table created.
SQL> select * from temptbl;
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7782 CLARK MANAGER 10
7839 KING PRESIDENT 10
7566 JONES MANAGER 20
7902 FORD ANALYST 20
7876 ADAMS CLERK 20
7369 SMITH CLERK 20
7521 WARD SALESMAN 30
7844 TURNER SALESMAN 30
7499 ALLEN SALESMAN 30
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7900 JAMES CLERK 30
7698 BLAKE MANAGER 30
14 rows selected.
SQL>
But, it is not giving me the last row of deptno. If I have to show the data on sqlplus then I can use break on skip 1, but since I have to create the table (around 10K rows) of my correct query, I just wish to insert a blank row after each grouped data.
Thank you.