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:
Post a Comment