I have a table with the following fields and data.
I want to get results based on the job column here is the expected result I want
SQL> create table emp
( empno NUMBER(4) constraint E_PK primary key
, ename VARCHAR2(8)
, init VARCHAR2(5)
, job VARCHAR2(8)
, mgr NUMBER(4)
, bdate DATE
, sal NUMBER(6,2)
, comm NUMBER(6,2)
, deptno NUMBER(2) default 10
11 ) ;
insert into emp values(1,'Tom','N', 'TRAINER', 13,date '1965-12-17', 800 , NULL, 20);
insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20', 1600, 300, 30);
insert into emp values(3,'Wil','TF' , 'Tester',6,date '1962-02-22', 1250, 500, 30);
insert into emp values(4,'Jane','JM', 'Designer', 9,date '1967-04-02', 2975, NULL, 20);
insert into emp values(5,'Mary','P', 'Tester',6,date '1956-09-28', 1250, 1400, 30);
insert into emp values(6,'Black','R', 'Designer', 9,date '1963-11-01', 2850, NULL, 30);
insert into emp values(7,'Chris','AB', 'Designer', 9,date '1965-06-09', 2450, NULL, 10);
insert into emp values(8,'Smart','SCJ', 'TRAINER', 4,date '1959-11-26', 3000, NULL, 20);
insert into emp values(9,'Peter','CC', 'Designer',NULL,date '1952-11-17', 5000, NULL, 10);
insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28', 1500, 0, 30);
insert into emp values(11,'Ana','AA', 'TRAINER', 8,date '1966-12-30', 1100, NULL, 20);
insert into emp values(12,'Jane','R', 'Manager', 6,date '1969-12-03', 800 , NULL, 30);
insert into emp values(13,'Fake','MG', 'TRAINER', 4,date '1959-02-13', 3000, NULL, 20);
insert into emp values(14,'Mike','TJA','Manager', 7,date '1962-01-23', 1300, NULL, 10);
CodePudding user response：
You can use the capabilities of formatting in sqlplus to get something very similar to what you want.
SQL> column job new_value jobvar noprint
SQL> ttitle left 'Job: ' jobvar skip 2
SQL> break on job skip page
SQL> btitle off
SQL> select * from emp order by job desc ;
Demo
SQL> select * from emp ;
EMPNO ENAME INIT JOB MGR BDATE SAL COMM DEPTNO
---------- -------- ----- -------- ---------- --------- ---------- ---------- ----------
1 Tom N TRAINER 13 17-DEC-65 800 20
2 Jack JAM Tester 6 20-FEB-61 1600 300 30
3 Wil TF Tester 6 22-FEB-62 1250 500 30
4 Jane JM Designer 9 02-APR-67 2975 20
5 Mary P Tester 6 28-SEP-56 1250 1400 30
6 Black R Designer 9 01-NOV-63 2850 30
7 Chris AB Designer 9 09-JUN-65 2450 10
8 Smart SCJ TRAINER 4 26-NOV-59 3000 20
9 Peter CC Designer 17-NOV-52 5000 10
10 Take JJ Tester 6 28-SEP-68 1500 0 30
11 Ana AA TRAINER 8 30-DEC-66 1100 20
EMPNO ENAME INIT JOB MGR BDATE SAL COMM DEPTNO
---------- -------- ----- -------- ---------- --------- ---------- ---------- ----------
12 Jane R Manager 6 03-DEC-69 800 30
13 Fake MG TRAINER 4 13-FEB-59 3000 20
14 Mike TJA Manager 7 23-JAN-62 1300 10
SQL> column job new_value jobvar noprint
SQL> ttitle left 'Job: ' jobvar skip 2
SQL> break on job skip page
SQL> btitle off
SQL> select * from emp order by job desc ;
Job: Tester
EMPNO ENAME INIT MGR BDATE SAL COMM DEPTNO
---------- -------- ----- ---------- --------- ---------- ---------- ----------
5 Mary P 6 28-SEP-56 1250 1400 30
3 Wil TF 6 22-FEB-62 1250 500 30
2 Jack JAM 6 20-FEB-61 1600 300 30
10 Take JJ 6 28-SEP-68 1500 0 30
Job: TRAINER
EMPNO ENAME INIT MGR BDATE SAL COMM DEPTNO
---------- -------- ----- ---------- --------- ---------- ---------- ----------
8 Smart SCJ 4 26-NOV-59 3000 20
1 Tom N 13 17-DEC-65 800 20
11 Ana AA 8 30-DEC-66 1100 20
13 Fake MG 4 13-FEB-59 3000 20
Job: Manager
EMPNO ENAME INIT MGR BDATE SAL COMM DEPTNO
---------- -------- ----- ---------- --------- ---------- ---------- ----------
14 Mike TJA 7 23-JAN-62 1300 10
12 Jane R 6 03-DEC-69 800 30
Job: Designer
EMPNO ENAME INIT MGR BDATE SAL COMM DEPTNO
---------- -------- ----- ---------- --------- ---------- ---------- ----------
9 Peter CC 17-NOV-52 5000 10
6 Black R 9 01-NOV-63 2850 30
4 Jane JM 9 02-APR-67 2975 20
7 Chris AB 9 09-JUN-65 2450 10
14 rows selected.