Pivot Table
Some times you need to generate rows from a SQL where not enough rows exist in relevant tables.
For example you might want to show the records from another table that falls in some ranges of values like
1-100 or JAN,FEB....DEC or SUN, MON, TUE...SAT etc.
The following query can be used to display all month names in oracle
SQL> select decode(rownum,1,'JAN', 2,'FEB',3,'MAR',4,'APR',5,'MAY',6,'JUN',7,'JUL',8,'AUG',9,'SEP',10,'OCT',11,'NOV',12,'DEC')
from all_objects where rownum<13;
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
Cross Tab Queries
Imagine you're trying to create a result set where the rows need to be columns, or vice versa.
In essence, you need to "pivot" rows into columns, or vice versa.
That is a very common requirement, and this is where you need to look at a crosstab query to get the job done.
Oracle has several tricks for allowing repeating groups of same-column values on a single line of output.
For example, assume that we need to display all degrees for an employee on a single row.
The view user_tab_columns stores all column names of a table.
It is possible to write a create table generation script using the query
select 'create table '||table_name||'('||
rtrim(max(decode(column_id,1,column_name||' '||data_type,null))||','||
max(decode(column_id,2,column_name||' '||data_type,null))||','||
max(decode(column_id,3,column_name||' '||data_type,null))||','||
max(decode(column_id,4,column_name||' '||data_type,null))||','||
max(decode(column_id,5,column_name||' '||data_type,null))||','||
max(decode(column_id,6,column_name||' '||data_type,null))||','||
max(decode(column_id,7,column_name||' '||data_type,null))||','||
max(decode(column_id,8,column_name||' '||data_type,null))||','||
max(decode(column_id,9,column_name||' '||data_type,null))||','||
max(decode(column_id,10,column_name||' '||data_type,null))||','||
max(decode(column_id,11,column_name||' '||data_type,null))||','||
max(decode(column_id,12,column_name||' '||data_type,null))||','||
max(decode(column_id,13,column_name||' '||data_type,null))||','||
max(decode(column_id,14,column_name||' '||data_type,null))||','||
max(decode(column_id,15,column_name||' '||data_type,null))||','||
max(decode(column_id,16,column_name||' '||data_type,null))||','||
max(decode(column_id,17,column_name||' '||data_type,null))||','||
max(decode(column_id,18,column_name||' '||data_type,null))||','||
max(decode(column_id,19,column_name||' '||data_type,null))||','||
max(decode(column_id,20,column_name||' '||data_type,null))||','||
max(decode(column_id,21,column_name||' '||data_type,null))||','||
max(decode(column_id,22,column_name||' '||data_type,null))||','||
max(decode(column_id,23,column_name||' '||data_type,null))||','||
max(decode(column_id,24,column_name||' '||data_type,null))||','||
max(decode(column_id,25,column_name||' '||data_type,null))||','||
max(decode(column_id,26,column_name||' '||data_type,null))||','||
max(decode(column_id,27,column_name||' '||data_type,null))||','||
max(decode(column_id,28,column_name||' '||data_type,null))||','||
max(decode(column_id,29,column_name||' '||data_type,null))||','||
max(decode(column_id,30,column_name||' '||data_type,null))||','||
max(decode(column_id,31,column_name||' '||data_type,null))||','||
max(decode(column_id,32,column_name||' '||data_type,null))||','||
max(decode(column_id,33,column_name||' '||data_type,null))||','||
max(decode(column_id,34,column_name||' '||data_type,null))||','||
max(decode(column_id,35,column_name||' '||data_type,null))||','||
max(decode(column_id,36,column_name||' '||data_type,null)),',')||')'
from user_tab_columns
where table_name='EMPLOYEE'
GROUP BY table_name;
Parsing Delimited Text and Join to the same table using Collections
Create TYPE
SQL> create type vartab as table of varchar2(100);
2 /
Type created.
Create Function to parse the string
SQL>
SQL> create function parse_string(str in varchar2, delim char) return vartab is
2 st1 vartab := vartab();
3 n1 number:=1;
4 n2 number;
5 n number:=0;
6 begin
7 LOOP
8 n2 := instr(str,delim, n1,1);
9 if n2>0 then
10 st1.extend;
11 n:=n+1;
12 st1(n) := substr(str,n1,n2-n1);
13 else
14 st1.extend;
15 n:=n+1;
16 st1(n) := substr(str,n1);
17 exit;
18 end if;
19 n1 := n2+1;
20 END LOOP;
21 for n1 in 1..n loop
22 dbms_output.put_line(st1(n1));
23 end loop;
24 return(st1);
25 end;
26 /
Function created.
Test the function from PL/SQL Block
SQL>
SQL>
SQL> set serveroutput ON
SQL>
SQL> DECLARE
2 t1 vartab;
3 BEGIN
4 t1 := parse_string('123 XYZ Null Null ABCD', ' ');
5 for c1 in(select * from TABLE(t1)) LOOP
6 dbms_output.put_line(c1.column_value);
7 END LOOP;
8 END;
9 /
123
XYZ
Null
Null
ABCD
123
XYZ
Null
Null
ABCD
PL/SQL procedure successfully completed.
Create Test Table and insert sample values
SQL>
SQL> create table mytab(id number, str varchar2(100));
Table created.
SQL>
SQL> insert into mytab values(1, '123 XYZ abcd efgh ABCD');
1 row created.
SQL> insert into mytab values(2, '345 PQRST JHGF 8796 0098');
1 row created.
SQL> insert into mytab values(3, '0021 ABC 3232 abcdefgh uvwxyz');
1 row created.
SQL> insert into mytab values(4, '001 AAA BBBB CCCCCC WWWWW');
1 row created.
Query the table
SQL>
SQL> select a.id,b.column_value from mytab a, table(parse_string(a.str,chr(32))) b;
1 123
1 XYZ
1 abcd
1 efgh
1 ABCD
2 345
2 PQRST
2 JHGF
2 8796
2 0098
3 0021
3 ABC
3 3232
3 abcdefgh
3 uvwxyz
4 001
4 AAA
4 BBBB
4 CCCCCC
4 WWWWW
20 rows selected.
123
XYZ
abcd
efgh
ABCD
345
PQRST
JHGF
8796
0098
0021
ABC
3232
abcdefgh
uvwxyz
001
AAA
BBBB
CCCCCC
WWWWW
SQL>
SQL>
SQL>
SQL> drop type vartab;
Drop test objects
Type dropped.
SQL> drop function parse_string;
Function dropped.
SQL> drop table mytab;
Table dropped.
|