Thank you for the easy to create test data ! I added a row to get at least one more row than columns, to make sure my code was working
SQL> CREATE TABLE TEACHER (NAME VARCHAR(20), PRO VARCHAR(20));
Table created.
SQL> INSERT INTO TEACHER VALUES ('A','PROFESSOR');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('B','DOCTOR');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('C','ENGINEER');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('D','PROFESSOR');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('E','DOCTOR');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('F','ENGINEER');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('G','DOCTOR');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('H','ENGINEER');
1 row created.
SQL> INSERT INTO TEACHER VALUES ('I','ENGINEER');
1 row created.
SQL>
SQL> select
2 rk,
3 max(decode(pro,'PROFESSOR',name)) c1,
4 max(decode(pro,'DOCTOR',name)) c2,
5 max(decode(pro,'ENGINEER',name)) c3
6 from
7 (
8 select
9 row_number() over ( partition by pro order by name ) rk,
10 name,
11 pro
12 from teacher
13 )
14 group by rk
15 order by 1;
RK C1 C2 C3
---------- -------------------- -------------------- --------------------
1 A B C
2 D E F
3 G H
4 I
4 rows selected.
SQL>
SQL>
SQL> select *
2 from
3 (
4 select
5 row_number() over ( partition by pro order by name ) rk,
6 name,
7 pro
8 from teacher
9 )
10 pivot ( max(name) as x for ( pro) in ( 'PROFESSOR' as c1, 'DOCTOR' as c2, 'ENGINEER' as c3 ))
11 order by 1;
RK C1_X C2_X C3_X
---------- -------------------- -------------------- --------------------
1 A B C
2 D E F
3 G H
4 I
4 rows selected.
SQL>
SQL>