Rows To Columns Or Pivot Table with SQL Model Clause In Oracle

A few days ago a question was asked in oracleturk mail group. It was about how to convert rows into columns. In my previous post, i have showed how to convert rows to columns. Now i studied SQL Model clause...

Demonstration below shows how to achieve it with Oracle's Model Clause.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL>
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> drop table test;

Table dropped

SQL> create table test(id varchar2(2), des varchar2(4), t number);

Table created

SQL> INSERT INTO test values('A','a1',12);

1 row inserted

SQL> INSERT INTO test values('A','a2',3);

1 row inserted

SQL> INSERT INTO test values('A','a3',1);

1 row inserted

SQL> INSERT INTO test values('B','a1',10);

1 row inserted

SQL> INSERT INTO test values('B','a2',23);

1 row inserted

SQL> INSERT INTO test values('C','a3',45);

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT * FROM test;

ID DES  T
-- ---- ----------
A  a1   12
A  a2   3
A  a3   1
B  a1   10
B  a2   23
C  a3   45

6 rows selected

SQL> select distinct i, A1, A2, A3
2 from test c
3 model
4 ignore nav
5 dimension by(c.id i,c.des d)
6 measures(c.t t, 0 A1, 0 A2, 0 A3)
7 rules(
8 A1[any,any] = t[cv(i),d = 'a1'],
9 A2[any,any] = t[cv(i),d = 'a2'],
10 A3[any,any] = t[cv(i),d = 'a3']
11 );

I  A1         A2         A3
-- ---------- ---------- ----------
C   0         0          45
B   10        23         0
A   12        3          1

SQL> select distinct d, A, B, C
2 from test c
3 model
4 ignore nav
5 dimension by(c.id i,c.des d)
6 measures(c.t t, 0 A, 0 B, 0 C)
7 rules(
8 A[any,any] = t[i = 'A', cv(d)],
9 B[any,any] = t[i = 'B', cv(d)],
10 C[any,any] = t[i = 'C', cv(d)]
11 );

D    A          B          C
---- ---------- ---------- ----------
a1   12         10         0
a3   1          0          45
a2   3          23         0

SQL> explain plan set statement_id 'menn' for
2 select distinct d, A, B, C
3 from test c
4 model
5 ignore nav
6 dimension by(c.id i,c.des d)
7 measures(c.t t, 0 A, 0 B, 0 C)
8 rules(
9 A[any,any] = t[i = 'A', cv(d)],
10 B[any,any] = t[i = 'B', cv(d)],
11 C[any,any] = t[i = 'C', cv(d)]
12 );

Explained

SQL> select plan_table_output from table(dbms_xplan.display('plan_table','menn'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 160770444
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT  | | 6  | 120   | 3 (34)     | 00:00:01 |
| 1 | HASH UNIQUE       | | 6  | 120   | 3 (34)     | 00:00:01 |
| 2 | SQL MODEL ORDERED | | 6  | 120   |            | |
| 3 | TABLE ACCESS FULL | TEST | 6     | 120 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

14 rows selected

SQL>

Notes On SQL MODEL Clause In Oracle

Oracle has introduced a very powerful mechanism called SQL Model to improve performance on SQL statements. Main idea of SQL Model clause is, to make possible some complex queries via SQL statements.

Model is based on arrays. MEASURES keyword identifies which arrays will be used. Indice of arrays are iddentified in DIMENSION BY statement. As a result, you can make array-based calculations in SQL easily.

In Model clause, business rules are taken into SQL. It is possible not to use a procedural language with model clause. One example of this can be found at the end of this entry with fibonacci numbers.

To make clear understanding of Model clause it'd better make some exercises:

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

SQL> DROP TABLE worker;

Table dropped

SQL> CREATE TABLE worker( id number primary key, name varchar2(32), salary number);

Table created

SQL> INSERT INTO worker VALUES (1, 'Mennan', 1200);

1 row inserted

SQL> INSERT INTO worker VALUES (2, 'Ali', 1500);

1 row inserted

SQL> INSERT INTO worker VALUES (3, 'Selim', 900);

1 row inserted

SQL> INSERT INTO worker VALUES (4, 'Ayse', 1450);

1 row inserted

SQL> INSERT INTO worker VALUES (5, 'Seyyah', 2900);

1 row inserted

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa)
  6  RULES();

         I N                                        SA
---------- -------------------------------- ----------
         1 Mennan                                 1200
         2 Ali                                    1500
         3 Selim                                   900
         4 Ayse                                   1450
         5 Seyyah                                 2900

In example above  n[i] descr,bes ith worker's name and sa[i] describes ith worker's salary. For instance n[1] is Mennan, sa[4] is 1450

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa)
  6  RULES(
  7  sa[any] = sa[cv()] + 10000 );

         I N                                        SA
---------- -------------------------------- ----------
         1 Mennan                                11200
         2 Ali                                   11500
         3 Selim                                 10900
         4 Ayse                                  11450
         5 Seyyah                                12900

CV() only can be used RIGHT-HAND SIDE and ANY only can be used LEFT-HAND SIDE of operators. sa[any] describes all employes' salaries.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, 0 temp, wr.salary sa)
  6  RULES(
  7  temp[any] = sa[cv()] + 10000 );

         I N                                      TEMP         SA
---------- -------------------------------- ---------- ----------
         1 Mennan                                11200       1200
         2 Ali                                   11500       1500
         3 Selim                                 10900        900
         4 Ayse                                  11450       1450
         5 Seyyah                                12900       2900

It is possible to use temporary arrays in MODEL clause.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa, cast('' as varchar2(32)) names )
  6  RULES(
  7  names[any] =  n[cv()] || '....' );

         I N                                        SA NAMES
---------- -------------------------------- ---------- --------------------------------
         1 Mennan                                 1200 Mennan....
         2 Ali                                    1500 Ali....
         3 Selim                                   900 Selim....
         4 Ayse                                   1450 Ayse....
         5 Seyyah                                 2900 Seyyah....

It is also possible to use string arrays with CAST operator.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa, cast('' as varchar2(32)) names )
  6  RULES ITERATE( 2 )(
  7  names[any] = names[CV()] || n[cv()] || '....' );

         I N                                        SA NAMES
---------- -------------------------------- ---------- --------------------------------
         1 Mennan                                 1200 Mennan....Mennan....
         2 Ali                                    1500 Ali....Ali....
         3 Selim                                   900 Selim....Selim....
         4 Ayse                                   1450 Ayse....Ayse....
         5 Seyyah                                 2900 Seyyah....Seyyah....

Iteration can be used in RULE section.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL IGNORE NAV
  4  DIMENSION BY(wr.id i)
  5  MEASURES( wr.salary sa)
  6  RULES UPSERT
  7  (
  8    sa[99999] = MAX(sa)[i BETWEEN 1 AND 3]
  9  );

         I         SA
---------- ----------
         4       1450
         5       2900
         1       1200
         2       1500
         3        900
     99999       1500

6 rows selected

UPSERT keyword is used for getting all rows. Some aggregate functions can be used in Model clause.

Example below shows how to calculate fibonacci series.

SQL> SELECT fi
  2    FROM dual
  3  MODEL
  4  DIMENSION BY( 1 i)
  5  MEASURES(1 b1, 1 b2, 0 fi )
  6  RULES ITERATE( 5 )(
  7  fi[any] = b1[cv()] + b2[cv()],
  8  b2[any] = b1[cv()],
  9  b1[any] = fi[cv()]
 10  );

        FI
----------
        13