CODEWITHSUNDEEP
sqloracle-database
Nwn
Nwn

Reputation: 571

Create a new view from existing table by duplicating the rows and changing the table structure

I have a table called "table1" which a has following rows.

ID  category1  category2
------------------------- 
1     value1    value2
2     value3    value4

Then I want to create a view from above "table1". In my view have only three columns. I introduced new column called "category" which can store the type of the category. Then both "category1" and "category2" column names belongs to that "category" column and introduced another new column called value to store the values of each category. The ID column is allowed to duplicate.

my generated view should be look like,

ID    category     value
--------------------------
1     category1    value1
1     category2    value2
2     category1    value3
2     category2    value4

I'm using Oracle. Is there anyway to do such a kind of thing? Is it possible or impossible?

Upvotes: 3

Views: 57

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Using Union[ All ] is enough :

create or replace view v_table1 as
select * from
(
  select t1.id, 'category1' category,t1.category1 value from table1 t1
  union all 
  select t2.id, 'category2' category, t2.category2 from table1 t2
)
order by id, value;

Upvotes: 3

Ronnis
Ronnis

Reputation: 12833

You can use unpivot functionality as well:

select * 
  from table1 unpivot(
        value for category in(category1, category2)
  );

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

A simple UNION (ALL) can do that:

SQL> WITH test (id, category1, category2)
  2       AS (SELECT 1, 'value1', 'value2' FROM DUAL
  3           UNION
  4           SELECT 2, 'value3', 'value4' FROM DUAL)
  5  SELECT id, 'category1' category, category1 VALUE FROM test
  6  UNION ALL
  7  SELECT id, 'categor12', category2 FROM test
  8  ORDER BY 1, 2;

        ID CATEGORY  VALUE
---------- --------- ------
         1 category1 value1
         1 categor12 value2
         2 category1 value3
         2 categor12 value4

SQL>

Upvotes: 1

Related Questions