1

I have a specific used case where a row values needs to be converted into view column headers dynamically. The reason why this needs a view is because the column names depends on the row value of the input table that can change anytime.

Input: Table

Item   Class   Attribute  Attribute_Val
------ ------- ---------- -------------
Apple  Fruit   Name       Gala 
Apple  Fruit   Color      Red 
Apple  Fruit   Origin     USA
Apple  Fruit   Price      3 
Mango  Fruit   Name       Alphonso
Mango  Fruit   Color      Yellow 
Mango  Fruit   Origin     MEX, IND
Mango  Fruit   Price      5

Expected Output: From a DB View

Item   Class   Name     Color  Origin  Price
------ ------- -----    ------ ------  -----
Apple  Fruit   Gala     Red    USA     3
Mango  Fruit   Alphonso Yellow MEX,IND 5

Let's say tomorrow a new attribute(Season) is added for class "Fruit". It will result in 2 rows in the input table(one each for Apple and Mango items) and the view should be able to dynamically add a new column "Season" to its output.

Is this something that can be done thru an Oracle View or Function?

APC
  • 144,005
  • 19
  • 170
  • 281
Cleo
  • 21
  • 1
  • 5
  • Probably a duplicate of https://stackoverflow.com/q/12069992/1509264 or https://stackoverflow.com/q/15491661/1509264 – MT0 Aug 31 '18 at 22:48

1 Answers1

1

Use a PIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Item, Class, Attribute, Attribute_Val ) AS
SELECT 'Apple', 'Fruit', 'Name',   'Gala'     FROM DUAL UNION ALL
SELECT 'Apple', 'Fruit', 'Color',  'Red'      FROM DUAL UNION ALL
SELECT 'Apple', 'Fruit', 'Origin', 'USA'      FROM DUAL UNION ALL
SELECT 'Apple', 'Fruit', 'Price',  '3'        FROM DUAL UNION ALL
SELECT 'Mango', 'Fruit', 'Name',   'Alphonso' FROM DUAL UNION ALL
SELECT 'Mango', 'Fruit', 'Color',  'Yellow'   FROM DUAL UNION ALL
SELECT 'Mango', 'Fruit', 'Origin', 'MEX, IND' FROM DUAL UNION ALL
SELECT 'Mango', 'Fruit', 'Price',  '5'        FROM DUAL;

Query 1:

SELECT *
FROM   table_name
PIVOT ( MAX( Attribute_Val ) FOR Attribute IN (
  'Name'   AS name,
  'Color'  AS color,
  'Origin' AS origin,
  'Price'  AS price
) )

Results:

|  ITEM | CLASS |     NAME |  COLOR |   ORIGIN | PRICE |
|-------|-------|----------|--------|----------|-------|
| Mango | Fruit | Alphonso | Yellow | MEX, IND |     5 |
| Apple | Fruit |     Gala |    Red |      USA |     3 |

Let's say tomorrow a new attribute(Season) is added for class "Fruit". It will result in 2 rows in the input table(one each for Apple and Mango items) and the view should be able to dynamically add a new column "Season" to its output.

No, this is not possible. A view has a fixed number of columns determined when you compile it and adding data (or columns) to the parent table will not update the view.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • MT0 - Thank you for the suggestion but this SQL won't work because in the IN clause you have hardcoded the 'Name', Color', 'Origin', 'Price'. Like I mentioned in the earlier requirement a new attribute like 'Season' can be added in the input table and we don't want to update the view to include it in the IN clause. – Cleo Aug 31 '18 at 22:53
  • @Cleo As I added at the bottom of my answer - what you are asking is not possible. Views have a fixed number of columns and you would need to drop the view and recreate it to change the columns. Apart from that a pivot also has a fixed number of columns so you will need to change the query manually or use PL/SQL (which is not compatible with a view). – MT0 Aug 31 '18 at 22:57
  • MT0 - Thanks for the clarification – Cleo Aug 31 '18 at 22:58