Create Column Names dynamically
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Create Column Names dynamically

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    Create Column Names dynamically

    Hello,

    I have the following task that I am having problems with.

    1. THIS IS DONE - Check for distinct MODEL_NAME value in TABLE 1 and see what MODEL_NAME value is missing in TABLE 2 (could be multiple).

    Here is SQL - THIS WORKS

    Code:
    SELECT DISTINCT a.device_type
    FROM logrelay.ra_device_type_hist@PRLDW a
    WHERE a.device_type is not null
        MINUS
    SELECT B.DEVICE_TYPE
    FROM rkiss.device_models b
    2. THIS IS DONE - Stored Prodecure that inserts new values into TABLE 2 with the following data, (id, dte, device_type) - this works as well.

    3, HERE IS MY PROBLEM.

    I have TABLE 3 where DEVICE_TYPE from TABLE 2 are column names. Currently I add column names manually, but would like to automate this process.

    I would like to create a process that would check any new DEVICE_TYPE values in TABLE 2 that does not exist as column names in TABLE 3. If new DEVICE_TYPE value exists in TABLE 2, create new column name (as value of the new DEVICE_TYPE in TABLE 2) in TABLE 3.

    I do not how to code this.

    Any ideas?

    Thanks

    Roman

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,440

    Cool

    Try this:
    Code:
    Begin
      For X In (Select Device_Type
                  From Rkiss.Device_Models
                Minus
                Select Column_Name 
                  From All_Tab_Columne
                 Where Owner='RKISS'
                   And Table_Name='TABLE3')
      Loop
        Execute Immediate 'Alter Table Table3 Add('
                       ||X.Device_Type||' Varchar2(10))';
      End Loop;
    End;
    /
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    I will give it a try.

    Thanks

    Roman

Posting Permissions

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



Click Here to Expand Forum to Full Width