Oracle SQL Query (Analytics?)

Question

I am trying to build a query such that some column are built up from a previous matching row. For example with the following data:

CREATE TABLE TEST (SEQ NUMBER, LVL NUMBER, DESCR VARCHAR2(10));
INSERT INTO TEST VALUES (1, 1, 'ONE');
INSERT INTO TEST VALUES (2, 2, 'TWO1');
INSERT INTO TEST VALUES (3, 2, 'TWO2');
INSERT INTO TEST VALUES (4, 3, 'THREE1');
INSERT INTO TEST VALUES (5, 2, 'TWO3');
INSERT INTO TEST VALUES (6, 3, 'THREE2');
COMMIT

I want the following data retrieved.

SEQ L1  L2   L3
1   ONE NULL NULL
2   ONE TWO1 NULL
3   ONE TWO2 NULL
4   ONE TWO2 THREE1
5   ONE TWO3 THREE1
5   ONE TWO3 THREE2

ie for row 3, it itself has the value for L2, for L1 it has to go to the most recent row that contains L1 data, in this case the first row.

I have tried looking at analytics and the connect clause but can't get my head around a solution.
Any ideas?

Answer 1

Do you have only 3 levels (or a fixed number of levels)?

If so, you could use something like that, which is very inefficient but I believe works (I can't run it from this computer so it's a "blind" code that may require slight alterations):

SELECT COUNTER.SEQ AS SEQ, A.DESCR AS L1, B.DESCR AS L2, C.DESCR AS L3
FROM TABLE AS COUNTER, TABLE AS A, TABLE AS B, TABLE AS C
WHERE
A.SEQ = 
    (SELECT MAX(D.SEQ) FROM TABLE AS D 
     WHERE D.LVL = 1 AND D.SEQ <= COUNTER.SEQ) AND 
B.SEQ = 
    (SELECT MAX(D.SEQ) FROM TABLE AS D 
     WHERE D.LVL = 2 AND D.SEQ <= COUNTER.SEQ) AND
C.SEQ = 
    (SELECT MAX(D.SEQ) FROM TABLE AS D 
     WHERE D.LVL = 3 AND D.SEQ <= COUNTER.SEQ)

Answer 2

To use the connect clause you would actually need some link betwean rows. So there should be some column that would have link to the previous row that has the wanted value. With those fields it is kinda difficult to make one select as you need to have 2 subselects for each row to check the other levels.

I would use pl/sql procedure if it is suitable.

declare
    cursor c_cur is
    select * from test order by seq asc;

lvl1 test.descr%type := null;
lvl2 test.descr%type := null;
lvl3 test.descr%type := null;

begin

for rec in c_cur loop
 if rec.lvl = 1 then
        lvl1 := rec.descr;
    elsif rec.lvl = 2 then
        lvl2 := rec.descr;
    elsif rec.lvl = 3 then
        lvl3 := rec.descr;
    end if;
    dbms_output.put_line(rec.seq||','||nvl(lvl1, 'null')||','||nvl(lvl2, 'null')||','||nvl(lvl3, 'null'));  
end loop;

end;
/

Answer 3

Hi Patrick,

Update: there is a much simpler solution than my first answer. It is more readable AND more elegant, I will therefore put it here first (As often, thanks to Tom Kyte):

SQL> SELECT seq,
  2         last_value(CASE
  3                       WHEN lvl = 1 THEN
  4                        descr
  5                    END IGNORE NULLS) over(ORDER BY seq) L1,
  6         last_value(CASE
  7                       WHEN lvl = 2 THEN
  8                        descr
  9                    END IGNORE NULLS) over(ORDER BY seq) L2,
 10         last_value(CASE
 11                       WHEN lvl = 3 THEN
 12                        descr
 13                    END IGNORE NULLS) over(ORDER BY seq) L3
 14    FROM TEST;

       SEQ L1         L2         L3
---------- ---------- ---------- ----------
         1 ONE                   
         2 ONE        TWO1       
         3 ONE        TWO2       
         4 ONE        TWO2       THREE1
         5 ONE        TWO3       THREE1
         6 ONE        TWO3       THREE2

Following is my initial solution:

SQL> SELECT seq,
  2         MAX(L1) over(PARTITION BY grp1) L1,
  3         MAX(L2) over(PARTITION BY grp2) L2,
  4         MAX(L3) over(PARTITION BY grp3) L3
  5    FROM (SELECT seq,
  6                 L1, MAX(grp1) over(ORDER BY seq) grp1,
  7                 L2, MAX(grp2) over(ORDER BY seq) grp2,
  8                 L3, MAX(grp3) over(ORDER BY seq) grp3
  9             FROM (SELECT seq,
 10                          CASE WHEN lvl = 1 THEN descr END L1,
 11                          CASE WHEN lvl = 1 AND descr IS NOT NULL THEN ROWNUM END grp1,
 12                          CASE WHEN lvl = 2 THEN descr END L2,
 13                          CASE WHEN lvl = 2 AND descr IS NOT NULL THEN ROWNUM END grp2,
 14                          CASE WHEN lvl = 3 THEN descr END L3,
 15                          CASE WHEN lvl = 3 AND descr IS NOT NULL THEN ROWNUM END grp3
 16                     FROM test))
 17   ORDER BY seq;

       SEQ L1         L2         L3
---------- ---------- ---------- ----------
         1 ONE                   
         2 ONE        TWO1       
         3 ONE        TWO2       
         4 ONE        TWO2       THREE1
         5 ONE        TWO3       THREE1
         6 ONE        TWO3       THREE2