CODEWITHSUNDEEP
sqloracle-databaseoracle11g
shubham
shubham

Reputation: 99

insert value in table with column name should be incremented while inserting

I have the table activity_log . in activity log table only one column is there and it contains 50 records around.

I have created a dummy/test table which contain ID as first column and dynamically created 50 column bcoz in activity log table 50 rows is there but now when inserting 50 rows values in 50 column . But i don't know how to insert/update 50 rows value into 50 column in dummy table?

declare

        i number;
        sql_stmnt  varchar2(400);
        xx   varchar2(400);
        b  varchar2(400);
        res number;
        end_char number;
        j number;
begin

        i:=0;
        xx:='a';
        end_char:=1;
        j:=1;

    select count(var_activity_pagetitle) into end_char  from activity_log;
    for i in 1..end_char loop 
            if i>=26 and i<=48 then
            select concat(xx,'a') into xx from dual;
            end if;
            if i>=49 and i<=74 then
            select concat(xx,'aa') into xx from dual;
            end if;
            if i>=75 and i<=100 then
            select concat(xx,'hh') into xx from dual;
            end if;

    sql_stmnt :=  'ALTER TABLE test_cursor_add ADD ' ||  xx ||  ' varchar2(1000)'; 

    dbms_output.put_line(sql_stmnt );
    Execute immediate  sql_stmnt ;
    dbms_output.put_line(i);

    select   ASCII( 'a' )+j into res  from dual;
        if j>=25 then
        j:=1;
        end if;

  select CHR( res ) into b from dual;
  xx:=b;

        if(i=50 ) then
        return;
        end if;

  j:=j+1;

  end loop;

end;

----

create table test_cursor_add ( id number);

Upvotes: 0

Views: 60

Answers (1)

Popeye
Popeye

Reputation: 35910

You can use PIVOT as following. It will generate the result containing 51 columns including id and 50 columns. You can then insert into your test_cursor_add table.

Insert into test_cursor_add
(Id,
Col1,
Col2,
...
Col50)
(Select * from
(Select 1 as id, t.var_activity_pagetitle,
        Row_number() over (order by null) as rn
        From activity_log t)
Pivot
(Max(var_activity_pagetitle) for rn in (1,2,3,...,50))

Cheers!!

Upvotes: 1

Related Questions