Oracle: materialized view with unique items out of flat table

I am working on 开发者_StackOverflowOracle. I have this large property-indexer table (THE_TABLE), whose columns are

[ObjectID | CategoryID | Property1 | Property2 | ... | PropertyN]

where N is known by design. All the Property% columns are VARCHAR2 of the same size, the ObjectID is Numeric. Now, I would like to create a materialized view that contains the results of the following queries:

select distinct CategoryID, (select 'Property1' from dual) as PropertyName, Property1 as PropertyValue from THE_TABLE

select distinct CategoryID, (select 'Property2' from dual) as PropertyName, Property2 as PropertyValue from THE_TABLE

...

select distinct CategoryID, (select 'PropertyN' from dual) as PropertyName, PropertyN as PropertyValue from THE_TABLE

In other words, the view (THE_VIEW) should contain three columns:

[CategoryID | PropertyName | PropertyValue]

and be able to return the unique values linked to a specific CategoryID. Is there a way to create such a view?

Please note that I do not have the possibility to modify THE_TABLE.

Regards, Andrea

I am not sufe if I understand your question correctly, but I believe you want something like

create materialied view THE_VIEW as
  select categoryID, 'property1' propertyName, property1 propertyValue from THE_TABLE union all
  select categoryID, 'property2' propertyName, property2 propertyValue from THE_TABLE union all
     ...
  select categoryID, 'propertyN' propertyName, propertyN propertyValue from THE_TABLE
;

If you're using Oracle 11g, you can try the new pivot/unpivot features.

This article is an excellent read on this, but in general you'd want an UNPIVOT in this case. For example (I'm not on an 11g instance at the moment, so this is a generic example):

SQL> SELECT *
  2  FROM   pivoted_data;

JOB           D10_SAL    D20_SAL    D30_SAL    D40_SAL
---------- ---------- ---------- ---------- ----------
CLERK            1430       2090       1045
SALESMAN                               6160
PRESIDENT        5500
MANAGER          2695     3272.5       3135
ANALYST                     6600

5 rows selected.


SQL> SELECT *
  2  FROM   pivoted_data
  3  UNPIVOT (
  4               deptsal                              --<-- unpivot_clause
  5           FOR saldesc                              --<-- unpivot_for_clause
  6           IN  (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
  7          );

JOB        SALDESC       DEPTSAL
---------- ---------- ----------
CLERK      D10_SAL          1430
CLERK      D20_SAL          2090
CLERK      D30_SAL          1045
SALESMAN   D30_SAL          6160
PRESIDENT  D10_SAL          5500
MANAGER    D10_SAL          2695
MANAGER    D20_SAL        3272.5
MANAGER    D30_SAL          3135
ANALYST    D20_SAL          6600

9 rows selected.

Another option for those on 11g.