Fw Converting Rows To Columns In Oracle

Converting Rows To Columns In Oracle
Sunday, September 24, 2006 3:50:28 PM (GTB Standard Time, UTC+02:00) ( 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.