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>