I have a table Test which contains
TEST
----
tablename|columnvalue|rankofcolumn
A|C1|1
A|C2|2
A|C3|3
A|C4|4
B|CX1|1
B|CX2|2
C|CY1|1
C|CY2|2
C|CY3|3
I want to generate the path along with other columns as follows
RESULT
----
tablename|columnvalue|rankofcolumn|path
A|C1|1|C1
A|C2|2|C1->C2
A|C3|3|C1->C2->C3
A|C4|4|C1->C2->C3->C4
B|CX1|1|CX1
B|CX2|2|CX1->CX2
C|CY1|1|CY1
C|CY2|2|CY1->CY2
C|CY3|3|CY1->CY2->CY3
As per this question, I can use recursive CTE to achieve this
WITH r ( tablename, columnvalue, rankofcolumn, PATH ) AS
(SELECT tablename,
columnvalue,
rankofcolumn,
columnvalue
FROM test
WHERE rankofcolumn = 1
UNION ALL
SELECT xx.tablename,
xx.columnvalue,
xx.rankofcolumn,
r.PATH || '->' || xx.columnvalue
FROM r
JOIN test xx
ON xx.tablename = r.tablename
AND xx.rankofcolumn = r.rankofcolumn + 1)
SELECT *
FROM r;
But I am using WX2 database which lacks this option at the moment. Is there a SQL alternative for this?
You could do the brute-force approach with a table that you gradually populate. Assuming your test table looks something like:
create table test (tablename varchar2(9), columnvalue varchar2(11), rankofcolumn number);
then the result table could be created with:
create table result (tablename varchar2(9), columnvalue varchar2(11), rankofcolumn number,
path varchar2(50));
Then create the result entries for the lowest rank:
insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn, t.columnvalue
from test t
where t.rankofcolumn = 1;
3 rows inserted.
And repeatedly add rows building on the highest existing rank, getting the following values (if there are any for that tablename) from the test table:
insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn,
concat(concat(r.path, '->'), t.columnvalue)
from test t
join result r
on r.tablename = t.tablename
and r.rankofcolumn = t.rankofcolumn - 1
where t.rankofcolumn = 2;
3 rows inserted.
insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn,
concat(concat(r.path, '->'), t.columnvalue)
from test t
join result r
on r.tablename = t.tablename
and r.rankofcolumn = t.rankofcolumn - 1
where t.rankofcolumn = 3;
2 rows inserted.
insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn,
concat(concat(r.path, '->'), t.columnvalue)
from test t
join result r
on r.tablename = t.tablename
and r.rankofcolumn = t.rankofcolumn - 1
where t.rankofcolumn = 4;
1 row inserted.
And keep going for the maximum possible number of columns (i.e. highest rankofcolumn for any table). You may be able to do that procedurally in WX2, iterating until zero rows are inserted; but you've made it sound pretty limited.
After all those iterations the table now contains:
select * from result
order by tablename, rankofcolumn;
TABLENAME COLUMNVALUE RANKOFCOLUMN PATH
--------- ----------- ------------ --------------------------------------------------
A C1 1 C1
A C2 2 C1->C2
A C3 3 C1->C2->C3
A C4 4 C1->C2->C3->C4
B CX1 1 CX1
B CX2 2 CX1->CX2
C CY1 1 CY1
C CY2 2 CY1->CY2
C CY3 3 CY1->CY2->CY3
Tested in Oracle but trying to avoid anything Oracle-specific; might need tweaking for WX2 of course.
