24 September 2006

Converting Rows To Columns In Oracle

Sometimes you can need to convert row values to spesific column values. In Oracle, it is possible to achive this goal by analytical functions. ROW_NUMBER analytical function is gets the number of like rownum  pseudocolumn. The difference is ROW_NUMBER gets partition based row numbers whereas rownum not.
I showed how to convert rows to columns in example below. The question is every class has at most 3 teacher. Give teacher names like TEACHER_1, TEACHER_2, TEACHER_3.


Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> drop table classes;

Table dropped
SQL> create table classes( class_name varchar2(1), teacher_name varchar2(32) );
Table created
SQL> insert into classes values('A','Margaritta Gonson');
1 row inserted
SQL> insert into classes values('A','Eric Billy');
1 row inserted
SQL> insert into classes values('A','Faruq Omar');
1 row inserted
SQL> insert into classes values('B','Antouan Schensez');
1 row inserted
SQL> insert into classes values('B','Michael Gabriella');
1 row inserted
SQL> insert into classes values('C','Haluk Gumuskaya');
1 row inserted
SQL> insert into classes values('D','Gokhan Yavuz');
1 row inserted
SQL> insert into classes values('D','Banu Diri');
1 row inserted
SQL> /
1 row inserted
SQL> SELECT * FROM classes;
CLASS_NAME TEACHER_NAME
---------- --------------------------------
A          Margaritta Gonson
A          Eric Billy
A          Faruq Omar
B          Antouan Schensez
B          Michael Gabriella
C          Haluk Gumuskaya
D          Gokhan Yavuz
D          Banu Diri
D          Banu Diri

9 rows selected
SQL> SELECT cc.class_name, cc.TEACHER_1, cc.TEACHER_2, cc.TEACHER_3
  2    FROM (SELECT MAX(CASE
  3                       WHEN MOD(r, 3) = 1 THEN
  4                        c.teacher_name
  5                       ELSE
  6                        NULL
  7                     END) TEACHER_1,
  8                 MAX(CASE
  9                       WHEN MOD(r, 3) = 2 THEN
 10                        c.teacher_name
 11                       ELSE
 12                        NULL
 13                     END) TEACHER_2,
 14                 MAX(CASE
 15                       WHEN MOD(r, 3) = 0 THEN
 16                        c.teacher_name
 17                       ELSE
 18                        NULL
 19                     END) TEACHER_3,
 20                 c.class_name
 21            FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
 22                         class_name,
 23                         teacher_name
 24                    FROM classes) c
 25           GROUP BY c.class_name
 26           ORDER BY c.class_name) cc;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz

SQL> --
SQL> SELECT c.class_name,
  2         MAX(decode(r, 1, c.teacher_name, NULL)) TEACHER_1,
  3         MAX(decode(r, 2, c.teacher_name, NULL)) TEACHER_2,
  4         MAX(decode(r, 3, c.teacher_name, NULL)) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   GROUP BY c.class_name
 10   ORDER BY c.class_name;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz

SQL> --
SQL> SELECT c.class_name,
  2         decode(r, 1, c.teacher_name, NULL) TEACHER_1,
  3         decode(r, 2, c.teacher_name, NULL) TEACHER_2,
  4         decode(r, 3, c.teacher_name, NULL) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   ORDER BY c.class_name;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                                                       
A                                           Faruq Omar                      
A                                                                            Margaritta Gonson
B          Antouan Schensez                                                 
B                                           Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                                                        
D                                           Banu Diri                       
D                                                                            Gokhan Yavuz

9 rows selected
SQL> --
SQL> SELECT c.class_name,
  2         MIN(decode(r, 1, c.teacher_name, NULL)) TEACHER_1,
  3         MIN(decode(r, 2, c.teacher_name, NULL)) TEACHER_2,
  4         MIN(decode(r, 3, c.teacher_name, NULL)) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   GROUP BY c.class_name
 10   ORDER BY c.class_name;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz

SQL>
As you can see above you can do this wtih CASE-WHEN or DECODE . The point of interest is using MAX or MIN is not important. Main aim of this aggregation function is to grouping records. If you do not use MIN or MAX, all records are taken and every row has only one teacher in it.

No comments: