1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

From Column to Data Value

Discussion in 'SQL PL/SQL' started by HeyThere, Apr 9, 2014.

  1. HeyThere

    HeyThere Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    First time asking a question here! Hopefully it's not too painful!

    I need to find out how to get the column name in Table1 to the data value in Table2?

    As an example;
    Table1

    Channel Reqs Time Max
    1 2 5 6
    2 3 7 8
    3 5 6 4


    Table2
    Channel Measure Value
    1 Reqs 2
    1 Time 5
    1 Max 6
    2 Reqs 3
    2 Time 7
    2 Max 8
    etc.

    I was thinking of doing something like this:
    insert into Table2(Channel, Measure, Value)
    (select
    Channel,
    'Reqs' as Measure,
    Value AS L1.Reqs)

    from Table1 L1

    I know it's bs! But would something like that work?
    How would I loop around so I could get Measure to equal the next column.

    Any help would be appreciated. (I wish above table examples would stay the way I typed them. Hopefully you'll understand what they're suppose to look like!!)
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    Try with the below insert statement.

    Code (SQL):
     INSERT INTO Table2(Channel, Measure, VALUE)
    SELECT Channel, 'Reqs',reqs  FROM Table1;
    Regards
    Sambasiva Reddy.K
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It is possible to insert all of the required data in one step:


    Code (SQL):

    SQL> CREATE TABLE table1(
      2          channel NUMBER,
      3          reqs    NUMBER,
      4          TIME    NUMBER,
      5          vmax    NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE table2(
      2          channel NUMBER,
      3          measure varchar2(6),
      4          mvalue  NUMBER);


    TABLE created.


    SQL>
    SQL> INSERT ALL
      2  INTO table1
      3  VALUES(1,2,5,6)
      4  INTO table1
      5  VALUES(2,3,7,8)
      6  INTO table1
      7  VALUES(3,5,6,4)
      8  SELECT * FROM dual;


    3 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> INSERT INTO table2(channel, measure, mvalue)
      2  (SELECT channel, 'Reqs', reqs FROM table1
      3   UNION
      4   SELECT channel, 'Time', TIME FROM table1
      5   UNION
      6   SELECT channel, 'Max', vmax FROM table1);


    9 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL>
    SQL> SELECT * FROM table2;


       CHANNEL MEASUR     MVALUE
    ---------- ------ ----------
             1 MAX             6
             1 Reqs            2
             1 TIME            5
             2 MAX             8
             2 Reqs            3
             2 TIME            7
             3 MAX             4
             3 Reqs            5
             3 TIME            6


    9 ROWS selected.


    SQL>
     
     
  4. HeyThere

    HeyThere Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks. Worked a treat. :hurray