Transform SQL

[james777]

What are the best ways to transform the below output in SQL.
I got the result by outer join the same table multiple times. Any help is appreciated.


Col1 Col2

A 1
B 2
C 3
D 4
E 5

Should transform to

Col1 Col2 ..................................col10
A 1
A 1 B 2
A 1 B 2 C 3
A 1 B 2 C 3 D 4
A 1 B 2 C 3 D 4 E 5


Thanks
Jim

 

[SantaMufasa]

So, Jim, are you saying that you want as many rows of output as there are rows in the table, and each row of output contains an accumulation of the contents of each of the previous rows?

If so, can you explain the business need for such a convolution?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[james777]

This is like key-value pairs in a set . Walking through the set and making them as links
starting from first to last . In a set the maximum links are 5 ( fixed).
Business is something related to hierarchial key pairs. Strange requirement though


PKID Col1 Col2

100 A 1
100 B 2
100 C 3
100 D 4
100 E 5

Should transform to

PKID Col1 Col2 ..................................col10
100 A 1
100 A 1 B 2
100 A 1 B 2 C 3
100 A 1 B 2 C 3 D 4
100 A 1 B 2 C 3 D 4 E 5

 

[SantaMufasa]

Do you want the output to be INSERTed into a table, or displayed to your screen? If to a table, what is your preference for a table name?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[james777]

Tablename should not be a problem (You name it). I am looking for best performed SQL.

Thanks
Jim

 

[james777]

Hi Mufasa,

CREATE TABLE PLAN
(
PLAN_ID NUMBER(10),
PLAN_KEY VARCHAR2(25),
PLAN_VAL NUMBER(1) CHECK(plan_val < 6)
)

INSERT INTO PLAN
SELECT 100,'A',1 FROM DUAL
UNION ALL
SELECT 100,'B',2 FROM DUAL
UNION ALL
SELECT 100,'C',3 FROM DUAL
UNION ALL
SELECT 100,'D',4 FROM DUAL
UNION ALL
SELECT 100,'E',5 FROM DUAL


Query with Outer joins:

SELECT p5.plan_id,p5.plan_key,p5.plan_val,
case p5.plan_val when 5 then p1.plan_key when 4 then p2.plan_key when 3 then p3.plan_key when 2 then p4.plan_key when 1 then p5.plan_key end kp1_key,
case p5.plan_val when 5 then p1.plan_val when 4 then p2.plan_val when 3 then p3.plan_val when 2 then p4.plan_val when 1 then p5.plan_val end kp1_val,
case p5.plan_val when 5 then p2.plan_key when 4 then p3.plan_key when 3 then p4.plan_key when 2 then p4.plan_key end kp2_key,
case p5.plan_val when 5 then p2.plan_val when 4 then p3.plan_val when 3 then p4.plan_val when 2 then p4.plan_val end kp2_val,
case p5.plan_val when 5 then p3.plan_key when 4 then p4.plan_key when 3 then p5.plan_key end kp3_key,
case p5.plan_val when 5 then p3.plan_val when 4 then p4.plan_val when 3 then p5.plan_val end kp3_val,
case p5.plan_val when 5 then p4.plan_key when 4 then p5.plan_key end kp4_key,
case p5.plan_val when 5 then p4.plan_val when 4 then p5.plan_val end kp4_val,
case p5.plan_val when 5 then p5.plan_key end kp5_key,
case p5.plan_val when 5 then p5.plan_val end kp5_val
FROM plan p1
right outer join plan p2 on p2.plan_val = p1.plan_val + 1
right outer join plan p3 on p3.plan_val = p2.plan_val + 1
right outer join plan p4 on p4.plan_val = p3.plan_val + 1
right outer join plan p5 on p5.plan_val = p4.plan_val + 1
ORDER BY p5.plan_val

 

[SantaMufasa]

So, what do you need/want me/us to do for you at this point?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[james777]

Looking for a better alternative solution, as this is costly with five FTS.

 

[SantaMufasa]

Jim,

Following is lightning-fast code that uses PL/SQL and no joins:

select * from plan;

PLAN_ID PLAN_KEY   PLAN_VAL
------- -------- ----------
    100 A                 1
    100 B                 2
    100 C                 3
    100 D                 4
    100 E                 5
    200 A                 1
    200 B                 2
    200 C                 3
    200 D                 4
    200 E                 5

10 rows selected.

create table plan2 (pkid number
,col1 char, col2 number
,col3 char, col4 number
,col5 char, col6 number
,col7 char, col8 number
,col9 char, col10 number);

SQL> desc plan2
 Name              Null?    Type
 ----------------- -------- -------
 PKID                       NUMBER
 COL1                       CHAR(1)
 COL2                       NUMBER
 COL3                       CHAR(1)
 COL4                       NUMBER
 COL5                       CHAR(1)
 COL6                       NUMBER
 COL7                       CHAR(1)
 COL8                       NUMBER
 COL9                       CHAR(1)
 COL10                      NUMBER

declare
    w plan2%rowtype;
    x plan2%rowtype;
    curr_plan number := 99999999999999;
    which_row number;
begin
    for y in (select * from plan order by 1,2) loop
        if y.plan_id <> curr_plan then
            curr_plan := y.plan_id;
            x:=w;
            x.pkid := y.plan_id;
            which_row := 0;
        end if;
        which_row := which_row+1;
        case when which_row = 1 then x.col1:=y.plan_key; x.col2:=y.plan_val;
             when which_row = 2 then x.col3:=y.plan_key; x.col4:=y.plan_val;
             when which_row = 3 then x.col5:=y.plan_key; x.col6:=y.plan_val;
             when which_row = 4 then x.col7:=y.plan_key; x.col8:=y.plan_val;
             when which_row = 5 then x.col9:=y.plan_key; x.col10:=y.plan_val;
        end case;
        insert into plan2 values(x.pkid,x.col1,x.col2,x.col3,x.col4,x.col5
                                ,x.col6,x.col7,x.col8,x.col9,x.col10);
    end loop;
    commit;
end;
/

PL/SQL procedure successfully completed.

col col1 format a4
col col3 like col1
col col5 like col1
col col7 like col1
col col9 like col1
col col2 format 9999
col col4 like col2
col col6 like col2
col col8 like col2
col col10 like col2
select * from plan2;

PKID COL1 COL2  COL3 COL4  COL5 COL6  COL7 COL8  COL9 COL10
---- ---- ----- ---- ----- ---- ----- ---- ----- ---- -----
 100 A        1
 100 A        1 B        2
 100 A        1 B        2 C        3
 100 A        1 B        2 C        3 D        4
 100 A        1 B        2 C        3 D        4 E        5
 200 A        1
 200 A        1 B        2
 200 A        1 B        2 C        3
 200 A        1 B        2 C        3 D        4
 200 A        1 B        2 C        3 D        4 E        5

10 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[james777]

Thanks a bunch.Excellent and elegant solution,Worked perfectly with a single FTS, performed very well.

But the requirement changed again not to duplicate the pair and to link with parent instead of generating a surrogate key for each key pairs.

CREATE TABLE PLAN
(
PLAN_ID NUMBER(10),
PLAN_KEY VARCHAR2(25),
PLAN_VAL NUMBER(1) CHECK(plan_val < 6),
PARENT_ID NUMBER(10)
)

INSERT INTO PLAN
SELECT 100,'A1',1,NULL FROM DUAL
UNION ALL
SELECT 101,'B1',2,100 FROM DUAL
UNION ALL
SELECT 102,'B2',2,100 FROM DUAL
UNION ALL
SELECT 103,'C1',3,101 FROM DUAL
UNION ALL
SELECT 104,'D1',4,103 FROM DUAL
UNION ALL
SELECT 113,'C2',3,102 FROM DUAL
UNION ALL
SELECT 105,'E1',5,104 FROM DUAL
UNION ALL
SELECT 106,'E2',5,104 FROM DUAL
UNION ALL
SELECT 123,'D2',4,113 FROM DUAL
UNION ALL
SELECT 124,'D3',4,113 FROM DUAL
UNION ALL
SELECT 133,'E5',5,123 FROM DUAL
UNION ALL
SELECT 134,'E6',5,123 FROM DUAL
UNION ALL
SELECT 135,'E7',5,123 FROM DUAL
UNION ALL
SELECT 144,'E8',5,124 FROM DUAL
UNION ALL
SELECT 146,'E9',5,124 FROM DUAL


Luckily, outer join query worked just by changing where clause to join with parent_id.

Query with Outer joins:

SELECT p5.plan_id,p5.plan_key,p5.plan_val,
case p5.plan_val when 5 then p1.plan_key when 4 then p2.plan_key when 3 then p3.plan_key when 2 then p4.plan_key when 1 then p5.plan_key end kp1_key,
case p5.plan_val when 5 then p1.plan_val when 4 then p2.plan_val when 3 then p3.plan_val when 2 then p4.plan_val when 1 then p5.plan_val end kp1_val,
case p5.plan_val when 5 then p2.plan_key when 4 then p3.plan_key when 3 then p4.plan_key when 2 then p4.plan_key end kp2_key,
case p5.plan_val when 5 then p2.plan_val when 4 then p3.plan_val when 3 then p4.plan_val when 2 then p4.plan_val end kp2_val,
case p5.plan_val when 5 then p3.plan_key when 4 then p4.plan_key when 3 then p5.plan_key end kp3_key,
case p5.plan_val when 5 then p3.plan_val when 4 then p4.plan_val when 3 then p5.plan_val end kp3_val,
case p5.plan_val when 5 then p4.plan_key when 4 then p5.plan_key end kp4_key,
case p5.plan_val when 5 then p4.plan_val when 4 then p5.plan_val end kp4_val,
case p5.plan_val when 5 then p5.plan_key end kp5_key,
case p5.plan_val when 5 then p5.plan_val end kp5_val
FROM plan p1
right outer join plan p2 on p2.plan_val = p1.parent_id
right outer join plan p3 on p3.plan_val = p2.parent_id
right outer join plan p4 on p4.plan_val = p3.parent_id
right outer join plan p5 on p5.plan_val = p4.parent_id
ORDER BY p5.plan_val

I tried PL/SQL approach in different ways but could not get the desired output like below.


100 A1 1
101 100 A1 1 B1 2
102 100 A1 1 B2 2
103 101 A1 1 B1 2 C1 3
113 102 A1 1 B2 2 C2 3
104 103 A1 1 B1 2 C1 3 D1 4
123 113 A1 1 B2 2 C2 3 D2 4
124 113 A1 1 B2 2 C2 3 D3 4
105 104 A1 1 B1 2 C1 3 D1 4 E1 5
106 104 A1 1 B1 2 C1 3 D1 4 E2 5
133 123 A1 1 B2 2 C2 3 D2 4 E5 5
134 123 A1 1 B2 2 C2 3 D2 4 E6 5
135 123 A1 1 B2 2 C2 3 D2 4 E7 5
144 124 A1 1 B2 2 C2 3 D3 4 E8 5
146 124 A1 1 B2 2 C2 3 D3 4 E9 5