Working with column permutations

Question

I am trying from the table below

key val
A   10
B   20
C   30
D   40

to achieve the following result. The label column displays all key combinationsIn alphabet order. The total column displays the addition of val s for the key s combination .

label total
A     10
AB    30
ABC   60
ABCD  100
AC    40
AD    50
B     20
BC    50
BCD   90
BD    60
C     30
CD    70
D     40

while it was possible to receive a request, still not so convinced of it. Look for the best ways to get the same set of results. thanks in advance.

with f (rn, key, val) as
(
  select rownum, a.* from
  (
      select 'A' key, 10 val from dual 
      union all select 'B', 20 from dual 
      union all select 'C', 30 from dual 
      union all select 'D', 40 from dual 
--      union all select 'E', 50 from dual
--      union all select 'F', 60 from dual
      order by 1
  ) a
)
,
-- irn, ikey, ival: anchor rownum, key and val to remember the starting row
-- rn, key, val: for the current row in the recursion
-- r1: current label in the recursion
-- r2: combination of anchor key and the current row key in the recursion
-- total: addition of all values for keys in r1
rs(irn, ikey, ival, rn, key, val, r1, r2, total) as
(
  select rn, key, val, rn, key, val, key, null, val from f
  union all
  select rs.irn, rs.ikey, rs.ival, f.rn, f.key, f.val, rs.r1 || f.key, rs.ikey || f.key, rs.total+f.val 
  from rs join f on (f.rn = rs.rn+1)
)
,
-- to add the additional rows required for the r2 col
-- when either r2 is not empty and not the same as r1 in rs
frs(irn, ikey, ival, rn, key, val, r1, r2, total) as
(
  select * from rs
  union all
  select irn, ikey, ival, rn, key, val, r2, r2, ival+val 
  from frs 
  where r2 is not null and r1 != r2
)
select r1, total from frs
order by 1
;

Answer 1

One way is to use the recursive factoring subquery available with Oracle 11.2:

with
-- Begin test data
     test_data ( key, val ) as (
       select 'A', 10 from dual union all
       select 'B', 20 from dual union all
       select 'C', 30 from dual union all
       select 'D', 40 from dual
     ),
-- End of test data (not part of the solution). 
-- SQL query begins with the keyword "with" from above and continues below this line.
     rec_cte ( label, total, last_symbol ) as (
       select key, val, key                                        -- anchor member
         from test_data
       union all
       select r.label || t.key, r.total + t.val, t.key             -- recursive member
         from rec_cte r join test_data t on r.last_symbol < t.key
     )
select   label, total 
from     rec_cte
order by label                         --  if needed
;

Output

LABEL TOTAL
----- -----
A        10
AB       30
ABC      60
ABCD    100
ABD      70
AC       40
ACD      80
AD       50
B        20
BC       50
BCD      90
BD       60
C        30
CD       70
D        40

15 rows selected.

Answer 2

PL/SQL block/Proceduere .

, t_perm , proc , . dbms_output. ..

create or replace procedure perm as
v_hold varchar2(20);
v_sum integer:=0;
cursor crs is select * from t_perm;
cursor crs1 is select * from t_perm;
begin
for rec in crs
loop
for rec1 in crs1
    loop
     if rec.key <= rec1.key then
         v_hold:=v_hold||rec1.key;
         v_sum:=v_sum+rec1.val;
         dbms_output.put_line(v_hold||' '||v_sum);
     end if;
    end loop;
v_hold:='';
v_sum:=0;
end loop;
end;

A 10
AB 30
ABC 60
ABCD 100
B 20
BC 50
BCD 90
C 30
CD 70
D 40