"Pivoting" a table/view in PLSQL

[boox]

Hi,
My requirement is to create a view by completely "rotating a table". I have tried many methods including 'Pivot' functions as well. Well, here's my problem in detail
I have a table as follows

Code: Select all

Product | Ingredients | Perecentage|Min|Max|
-------------------------------------------------------
P100    | A                |20               |4    |25   |
P100    | B                | 30              |8     |44  |
P100    |C                 | 33              |7     |50  |

The requirment is to have a view like this, when the 'Product' is specified, the View should look like this

Code: Select all

Ingredient | A  |B  | C|
-----------------------------
Percentage|20|30|33 |
Min           |  4|  8|  7|
Max          |25 |44|50|

I managed to get it for Percentage, though I am yet to figure out how to ge the 'Percentage' in a column.... this is my query

Code: Select all

SELECT *
FROM   (SELECT Ingredient , Percentage
        FROM   product_table WHERE Product =P164)
PIVOT  (SUM(Percentage) AS sum_percent FOR (Ingredient ) IN ('A' AS A, 'B' AS b, 'C' AS C))

this gives the desired result for Percentage - but i am clueless how to do this for all 3 fields : Percentage, Min and Max...
Any Ideas are more than welcome!!!

[Tim...]

Hi.

When you post this type of question you should post a setup script like this.

Code: Select all

create table product_table (
  product VARCHAR2(10),
  ingredient varchar2(10),
  percentage  number,
  minimum number,
  maximum number
);
 
insert into product_table values ('P100','A', 20, 4, 25);
insert into product_table values ('P100','B', 30, 8, 44);
insert into product_table values ('P100','C', 33, 7, 50);
commit;

You *must* make it as easy as possible for people to help you, or they may not bother.

This query seems to do what you want.

Code: Select all

SELECT col1 AS "Ingredient",
       a_sum_percent AS "A",
       b_sum_percent AS "B",
       c_sum_percent AS "C"
FROM   (      
      SELECT *
      FROM   (SELECT 'percentage' as col1, Ingredient , Percentage
            FROM   product_table WHERE Product ='P100') a
      PIVOT  (SUM(Percentage) AS sum_percent FOR (Ingredient ) IN ('A' AS A, 'B' AS b, 'C' AS C))
      UNION ALL
      SELECT *
      FROM   (SELECT 'min' as col1, Ingredient , minimum
            FROM   product_table WHERE Product ='P100')
      PIVOT  (SUM(minimum) AS sum_minimum FOR (Ingredient ) IN ('A' AS A, 'B' AS b, 'C' AS C))
      UNION ALL
      SELECT *
      FROM   (SELECT 'max' as col1, Ingredient , maximum
            FROM   product_table WHERE Product ='P100')
      PIVOT  (SUM(maximum) AS sum_maximum FOR (Ingredient ) IN ('A' AS A, 'B' AS b, 'C' AS C))
       );

Ingredient          A          B          C
---------- ---------- ---------- ----------
percentage         20         30         33
min                 4          8          7
max                25         44         50

SQL>

Cheers

Tim...