dynamic record should be generate based on id column.in table we have record like id 1, having std value(in roman format) (i),(ii),(iii) then record generated should be for id 1 with std value(in roman format till 6) (i),(ii),(iii),(iv),(v),(vi). but if id 1 is also having code column value then std column will be null but will have grade and code value. Below is table view of actual record and expected record.
actual record(data saved in table):-
name id std grade code
ram-1 142174 (i) V
ram-1 142174 (ii) N
ram-1 142174 (iii) X
ram-1 142174 (iv) X
ram-1 142174 A A
ram-2 140965 (i) N
ram-2 140965 (iii) X
ram-3 140966 B B
expected record:-
name id std grade code
ram-1 142174 (i) V
ram-1 142174 (ii) N
ram-1 142174 (iii) X
ram-1 142174 (iv) X
ram-1 142174 (v)
ram-1 142174 (vi)
ram-1 142174 A A
ram-2 140965 (i) N
ram-2 140965 (ii)
ram-2 140965 (iii) X
ram-2 140965 (iv)
ram-2 140965 (v)
ram-2 140965 (vi)
ram-3 140966 (i)
ram-3 140966 (ii)
ram-3 140966 (iii)
ram-3 140966 (iv)
ram-3 140966 (v)
ram-3 140966 (vi)
ram-3 140966 B B
here is my approach, using with as clause.
with cte (id, name, std, grade , code) as (
select distinct id, name, 1, grade , code from t11
union all
select id, name, std + 1, grade , code from cte where std < 6
)
select id, name, '('||trim(lower(to_char(std,'RN')))||')' std, grade , code
from cte
order by id, stdsample table create script:-
create table t11
(
name varchar2(20),
id number,
standard varchar2(20),
grade varchar2(20),
code varchar2(20)
);
insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(1,'RN')))||')','V',null);
insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(2,'RN')))||')','N',null);
insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(3,'RN')))||')','X',null);
insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(4,'RN')))||')','X',null);
insert into t11 values('ram-1', 142174, null,'A','A');
insert into t11 values('ram-2', 140965, '('||trim(lower(to_char(1,'RN')))||')','N',null);
insert into t11 values('ram-2', 140965, '('||trim(lower(to_char(3,'RN')))||')','X',null);
insert into t11 values('ram-3', 140966,null,'B','B');
Partition outer joining to a 6-row table on standard, then union alling the rows where standadr is null looks like it'll do the trick to me:
with rws as (
select level n, '(' || trim ( to_char ( level, 'rn' ) ) || ')' rn
from dual
connect by level <= 6
)
select t11.name,
t11.id,
rws.rn,
t11.grade,
t11.code,
rws.n
from rws
left join t11
partition by ( name, id )
on ( rn = standard )
union all
select t11.*, null n
from t11
where standard is null
order by name, id, n;
NAME ID RN GRADE CODE N
ram-1 142174 (i) V <null> 1
ram-1 142174 (ii) N <null> 2
ram-1 142174 (iii) X <null> 3
ram-1 142174 (iv) X <null> 4
ram-1 142174 (v) <null> <null> 5
ram-1 142174 (vi) <null> <null> 6
ram-1 142174 <null> A A <null>
ram-2 140965 (i) N <null> 1
ram-2 140965 (ii) <null> <null> 2
ram-2 140965 (iii) X <null> 3
ram-2 140965 (iv) <null> <null> 4
ram-2 140965 (v) <null> <null> 5
ram-2 140965 (vi) <null> <null> 6
ram-3 140966 (i) <null> <null> 1
ram-3 140966 (ii) <null> <null> 2
ram-3 140966 (iii) <null> <null> 3
ram-3 140966 (iv) <null> <null> 4
ram-3 140966 (v) <null> <null> 5
ram-3 140966 (vi) <null> <null> 6
ram-3 140966 <null> B B <null>