This forum is currently locked. You can't register or post questions at this time. (read more)

"Pivoting" a table/view in PLSQL

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

boox
Member
Posts: 1
Joined: Mon Jun 18, 2012 8:18 am

"Pivoting" a table/view in PLSQL

Postby boox » Mon Jun 18, 2012 8:43 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: "Pivoting" a table/view in PLSQL

Postby Tim... » Mon Jun 18, 2012 9:31 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests