Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Amit.

Asked: June 28, 2016 - 12:03 pm UTC

Last updated: June 29, 2016 - 4:28 am UTC

Version: Oracle 9 i

Viewed 1000+ times

You Asked

Hi Tom,

I have a table:

CREATE TABLE TEACHER (NAME VARCHAR(20), PRO VARCHAR(20));

with this data in it:
INSERT INTO TEACHER VALUES ('A','PROFESSOR');
INSERT INTO TEACHER VALUES ('B','DOCTOR');
INSERT INTO TEACHER VALUES ('C','ENGINEER');
INSERT INTO TEACHER VALUES ('D','PROFESSOR');
INSERT INTO TEACHER VALUES ('E','DOCTOR');
INSERT INTO TEACHER VALUES ('F','ENGINEER');
INSERT INTO TEACHER VALUES ('G','DOCTOR');
INSERT INTO TEACHER VALUES ('H','ENGINEER');

I want the output like :

DOCTOR ENGINEER PROFESSOR
B C A
E F D
G H NULL

How can we achieve this with and without PIVOT function.

Thanks
Amit

and Connor said...

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>


Rating

  (2 ratings)

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

Comments

A reader, April 18, 2018 - 12:45 pm UTC


A reader, April 18, 2018 - 12:47 pm UTC


More to Explore

Analytics

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