Skip to Main Content
  • Questions

  • Oracle Sql to simulate skipping titles

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pravas.

Asked: January 23, 2016 - 7:24 pm UTC

Last updated: March 21, 2018 - 11:29 am UTC

Version: 11gr2

Viewed 1000+ times

You Asked

Sir in my Emp table I want to see all the jobs with corresponding employees in that designation simultaneously without repeating the Job name...like below.

Example
-----------------
JOB ENAME
------- ---------------
CLERK SMITH
ADAMS
JAMES
MILLER
PRESIDENT KING
MANAGER BLAKE
JONES
CLARK



LIKE THIS I WANT ALL EMPLOYEES WITH THEIR DESIGNATIONS...

and Connor said...

If you mean using (say) SQL Plus, then the tool itself can do it without altering the SQL, eg

SQL> select ename, job from emp order by job, ename;

ENAME      JOB
---------- ---------
FORD       ANALYST
SCOTT      ANALYST
ADAMS      CLERK
JAMES      CLERK
MILLER     CLERK
SMITH      CLERK
BLAKE      MANAGER
CLARK      MANAGER
JONES      MANAGER
KING       PRESIDENT
ALLEN      SALESMAN
MARTIN     SALESMAN
TURNER     SALESMAN
WARD       SALESMAN

14 rows selected.

SQL>
SQL> break on job
SQL> select ename, job from emp order by job, ename;

ENAME      JOB
---------- ---------
FORD       ANALYST
SCOTT
ADAMS      CLERK
JAMES
MILLER
SMITH
BLAKE      MANAGER
CLARK
JONES
KING       PRESIDENT
ALLEN      SALESMAN
MARTIN
TURNER
WARD

14 rows selected.


If you mean by pure SQL, you can use an analytic function


SQL>
SQL>
SQL>
SQL> select
  2    ename,
  3    case when r = 1 then job end jobx
  4  from
  5    ( select ename, job , row_number() over ( partition by job order by ename ) as r
  6      from emp
  7  )
  8  order by job, ename;

ENAME      JOBX
---------- ---------
FORD       ANALYST
SCOTT
ADAMS      CLERK
JAMES
MILLER
SMITH
BLAKE      MANAGER
CLARK
JONES
KING       PRESIDENT
ALLEN      SALESMAN
MARTIN
TURNER
WARD

14 rows selected.


If you want to know more about analytics, try my video series on it

https://www.youtube.com/playlist?list=PLJMaoEWvHwFJDyhMLCkNSSUQWw9waFkIj

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot sir..

Pravas Kumar, January 24, 2016 - 6:36 am UTC


SKIP 1 line after Break

Utpal, March 20, 2018 - 11:08 pm UTC

Can a SKIP 1 line after Break be done in pure SQL?
Connor McDonald
March 21, 2018 - 11:29 am UTC

It could, but I dont really see the point - that is a formatting issue, which you would typically do with the tool consuming the output. But anyway...

SQL> with t as
  2        ( select ename, job ,
  3                 row_number() over ( partition by job order by ename ) as r,
  4                 count(*) over ( partition by job ) c
  5          from emp
  6      )
  7  select ename, jobx
  8  from (
  9  select
 10        ename ename,
 11        case when r = 1 then job end jobx,
 12        r, c, job
 13      from t
 14  union all
 15  select null, null , c+1, null, job from t
 16  where r=1
 17  order by job, ename
 18  );

ENAME      JOBX
---------- ---------
FORD       ANALYST
SCOTT

ADAMS      CLERK
JAMES
MILLER
SMITH

BLAKE      MANAGER
CLARK
JONES

KING       PRESIDENT

ALLEN      SALESMAN
MARTIN
TURNER
WARD


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.