Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Thumbs up Unanswered: Column to Row Translation

    I have one table with 200 columns and n number of rows .. I want to create another table which will have following structure

    Tablename, columnname, valueincol

    now in this table I want to populate all column values from the above table .. now I don't know what are the columnnames and number of columns in the original table .. so lets assume name of the table is t1 and it has x1,x2,x3 columns and their values are

    10,20,30

    I want to create a new table with rows like

    t1 x1 10
    t1 x2 20
    t1 x3 30

    Tom, I have used an approach like getting column names from user_tab_columns,and putting them along with table name .. but when it comes to poulating its value I have to make many calls .. here in this case 3 ... to get that data .. can you please suggest some good way of doing it ?


    Please help

  2. #2
    Join Date
    May 2003
    Posts
    87
    Here is the bare skeleton pl/sql script. You can convert it into a procedure/function to be able to pass table name.

    Also, you will have to ignore if you have any long/raw columns. If you have long data type columns, only a limited data for that column can be brought in. Or you can have 1 long datatype column and code accordingly.

    Hope this gets you started !!

    Code:
    declare
      dynasql varchar2(1000);
    begin
      for c_tabcols in (select table_name, column_name
                          from all_tab_columns
                         where table_name = 'T1') loop
        
        dynasql := 'insert into new_table select '''||
                    c_tabcols.table_name||''','''||
                    c_tabcols.column_name||''','||
                    c_tabcols.column_name||' from '||c_tabcols.table_name;
        execute immediate dynasql;
      end loop;
      commit;
    end;
    /

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Column to Row Translation

    I would suggest a cursor in an pl/sql block. Here is what I had in mind.

    This will produce an insert statement that will populate your second table.

    declare

    -- cursor of columns names for the table
    cursor c_columns(p_table_name varchar2) is
    select column_name
    from user_tab_cols
    where table_name = p_table_name;

    v_sql_text varchar2(250);

    begin

    -- Enter the table name to translate
    for cols in c_columns('Table1') loop

    -- build sql statement that will insert the records
    v_sql_text := 'insert into new_table(Tablename, columnname, valueincol) select ''MMU_FUEL_PRICES'', '''|| cols.column_name||''''||',cols.column_name from MMU_FUEL_PRICES;';

    -- You can either print these statements and run the output
    dbms_output.put_line(v_sql_text);

    -- or use execute immediate and run them while you parse through
    execute immediate v_sql_text;

    end loop;

    -- needed if you want to use the execute immediate to insert the data
    commit;

    end;

    Originally posted by ybhandarkar
    I have one table with 200 columns and n number of rows .. I want to create another table which will have following structure

    Tablename, columnname, valueincol

    now in this table I want to populate all column values from the above table .. now I don't know what are the columnnames and number of columns in the original table .. so lets assume name of the table is t1 and it has x1,x2,x3 columns and their values are

    10,20,30

    I want to create a new table with rows like

    t1 x1 10
    t1 x2 20
    t1 x3 30

    Tom, I have used an approach like getting column names from user_tab_columns,and putting them along with table name .. but when it comes to poulating its value I have to make many calls .. here in this case 3 ... to get that data .. can you please suggest some good way of doing it ?


    Please help

  4. #4
    Join Date
    Jun 2003
    Posts
    4

    DB calls

    Hi, Thanx for reply ...

    As I mentioned earlier I have tables with 200 columns or so .. so it will fire inser statement 200 times .... so I was avoiding this .. is there is any other way where we can deduce DB calls .. as the application is time sensetive and like these there are 3-4 tables in queue so almose 800 inserts .. so need to do minimize DB calls

    Thanks once again

  5. #5
    Join Date
    Jun 2003
    Posts
    4

    Re: Column to Row Translation

    Well I can use this approach if I have once schema and tables into it .. but I even don't know which schema the table belongs to .. so I need to use statement like

    v_SelectString:='SELECT * FROM '||p_SchemaName||'.'||v_Termtable ||WHERE '||v_Termcolnm||' = '||v_id;

    for this I can't use parameterized cursor ... I have already tried this

    See I ahve these I/p parameters

    p_id
    p_SchemaName
    p_VirtualTab
    p_LocSeqId

    So I can't use

    cursor c_columns(p_table_name varchar2) is
    select column_name
    from user_tab_cols
    where table_name = p_table_name;

    Any solution for this ?

    Thanks once again

  6. #6
    Join Date
    Jun 2003
    Posts
    4

    Re: Column to Row Translation

    I would like to add more to previous information ....

    p_id
    p_SchemaName
    p_VirtualTab
    p_LocSeqId

    Now from p_LocSeqId I will get the actual table name and the column name on which the condition is based ...

    Means .. I will say

    SELECT Termtable,Termcolnm FROM Tabloc WHERE Locseqid = p_LocSeqId;

    and by using this process further ...

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •