1

I have a table which looks like this:

table1

column1 column2 column3
1       2       3 
a       6       9 
b       8       7

I need to copy this data to another table in this format:

table2

column_name value
column1     1
column1     a
column1     b
column2     2
column2     6
column2     8
column3     3
column3     9
column3     7

There are more than 100 tables like table1 with millions of rows. I need to copy this in table2.

So, I was planning to write like:

INSERT INTO table2
(column_name, value)
SELECT 'column1', column1
FROM table1;

INSERT INTO table2
(column_name, value)
SELECT 'column2', column2
FROM table1;

INSERT INTO table2
(column_name, value)
SELECT 'column3', column3
FROM table1;

I have 80 columns like this. Is there an efficient way of writing this which also works fast in copying data in Oracle?

Edit: changed example dataset to include different type of data. If I use unpivot, it gives me:

ORA-01790: expression must have same datatype as corresponding expression

Improve this question
4
  • 1
    I don't know why in the hell is there a requirement like above. But if this has to be done just generate the 80 insert statements like above and run them all at once. Sometimes you have to be brutal and trouble the hardware. Commented Nov 11, 2015 at 12:29
  • @VarunRao - is it bad to have key value structure? Is there no way to optimise this? Commented Nov 11, 2015 at 12:31
  • Write a Java application or PL/SQL script program to handle it. Commented Nov 11, 2015 at 12:32
  • @dang key value structures are typically frowned upon in well-designed RDBMS's. It's much harder to query, for a start. Commented Nov 11, 2015 at 12:39

2 Answers 2

Reset to default
5

Use unpivot for that 

 select * from table1
    unpivot
    (
    "values" for column_names in (column1,column2,column3))

Edit: Insert statement for above for populate data into another

create table table2
(
column_names varchar2(1),
"values" varchar2(1)
)
/
    insert into table2
    select * from table1
        unpivot
        (
        "values" for column_names in (column1 as '1',column2 as '2',column3 as '3'))

Update It will replace column1 with 1, column2 with 2 etc

 select * from table1
        unpivot
        (
        "values" for column_names in (column1 as '1',column2 as '2',column3 as '3'))
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

there is syntax issue in the answer.
I have updated the query once check it now. If u get error still please post it.
I forgot to close parenthesis now i have edited please check.
Instead of column names, I have to use ID, so column1 would be replaced by 1, column2 would be replaced by 2. How can I do it using unpivot?
Also, note the data is of different datatype, so giving error - ORA-01790: expression must have same datatype as corresponding expression
|
0

Here are a couple of ways of unpivoting the data - one is the manual way, and one is using the UNPIVOT keyword. You'd have to test both to see which one works best for your set of data.

with sample_data as (select '1' column1, 2 column2, 3 column3 from dual union all
                     select 'a' column1, 6 column2, 9 column3 from dual union all
                     select 'b' column1, 8 column2, 7 column3 from dual)
select d.id column_id,
       case when d.id = 1 then sd.column1
            when d.id = 2 then to_char(sd.column2)
            when d.id = 3 then to_char(sd.column3)
       end value
from   sample_data sd
       cross join (select level id
                   from   dual
                   connect by level <= 3) d;

COLUMN_NAME VALUE                                   
----------- ----------------------------------------
          1 1                                       
          1 a                                       
          1 b                                       
          2 2                                       
          2 6                                       
          2 8                                       
          3 3                                       
          3 9                                       
          3 7   

with sample_data as (select '1' column1, 2 column2, 3 column3 from dual union all
                     select 'a' column1, 6 column2, 9 column3 from dual union all
                     select 'b' column1, 8 column2, 7 column3 from dual)
select *
from   (select column1,
               to_char(column2) column2,
               to_char(column3) column3
        from   sample_data) sd
unpivot (value for column_id in (column1 as '1', column2 as '2', column3 as '3')) d;

COLUMN_ID VALUE                                   
--------- ----------------------------------------
1         1                                       
2         2                                       
3         3                                       
1         a                                       
2         6                                       
3         9                                       
1         b                                       
2         8                                       
3         7

In both queries, I've used the WITH clause (aka subquery-factoring aka CTE) to mimic a table with data in. In your case, you wouldn't need the with clause.

To insert the data into another table, you'd just have to append the insert clause before the sql statement, eg:

insert into other table (column_id, value)
select column_id,
       value
from   (select column1,
               to_char(column2) column2,
               to_char(column3) column3
        from   sample_data) sd
unpivot (value for column_id in (column1 as '1', column2 as '2', column3 as '3')) d;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.