ORCL - Lag/Lead Over

In normalized form, the data is display as the row format, such as the following:

12:28:14 PM SQL> select id_number, degree_code from degrees where id_number = 'xxxx';

ID_NUMBER DEGREE_CODE
---------- -----------
xxxxxxxxxx PHD
xxxxxxxxxx ENG
xxxxxxxxxx MSC

But we want the result displasy as one row as different columns, basically we want the data de-normlized.

A Little SQL

If we know the identity for the degree code then we can construct a SQL statement that will handle this type of query using Rotate/Pivot Query.

First we want to get such identity, so we use the windows over function to assign the row number to identify the degree code:

12:28:50 PM SQL> select id, row_number() over (partition by id order by code) rn, code from degrees;
ID RN CODE
---------- ---------- -----------
000000xxxx 1 NUSSS
000000xxx1 1 EDBSS
000000xxx2 1 MAAAA
000000xxx2 2 PHDSS
000000xxx3 1 BCZZZ
000000xxx3 2 SCESS

The we can change the row to colum display using the code shown below:
12:35:47 PM SQL>
12:40:20 PM SQL> select id,
2 (select d1.code from
3 (select id, row_number() over (partition by id order by code) rn, code
4 from table_name) d1
5 where d1.id = A.id and rn =1) degree1,
6 (select d1.code from
7 (select id, row_number() over (partition by id order by code) rn, code
8 from table_name) d1
9 where d1.id = A.id and rn =2) degree2,
10 (select d1.code from
11 (select id, row_number() over (partition by id order by code) rn, code
12 from table_name) d1
13 where d1.id = A.id and rn =3) degree3,
14 (select d1.code from
15 (select id, row_number() over (partition by id order by code) rn, code
16 from table_name) d1
17 where d1.id = A.id and rn =4) degree4
18 from (select id from table_name group by id) A
19 /

Then we achieve what we want!

But if you use the Lag/lead over analytic function, it is even better!

SQL> select id, d1, d2, d3, d4 from
2 (select id, sequence, degree_code d1, lead(degree_code,1) over (order by sequence) d2,
3 lead(degree_code,2) over (order by sequence) d3, lead(degree_code,3) over (order by sequence) d4
4 from tale_name
5 where id= '0000000xxx')
6 where sequence = 1
7 /

ID D1 D2 D3 D4
---------- ----- ----- ----- -----
0000000xxx PHD ENG MSC

SQL> here we go! Analytic functions rock for 80% case!