在Oracle中将行值放入列标题中

Question

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

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

这可以通过Oracle视图或函数来完成吗？

Answer 1

使用透视:

SQL小提琴

Oracle 11g R2架构设置：

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;

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

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