Home » SQL & PL/SQL » SQL & PL/SQL » How to make a view with rows as columns (Oracle 10G R2)
How to make a view with rows as columns [message #403570] Sun, 17 May 2009 02:33 Go to next message
sanjeev_thoppil
Messages: 4
Registered: May 2009
Junior Member
I have a table PRODUCT_DIMENSIONS which stores the properties of a product. These properties are user-defined and the
user can define the properties as per his requirements. In my below example , my product A and B, is defined by 4 parameters
Grade, Width,Thick, Length, Angle . But the product C is defined by 3 parameters only.


I WANT TO MAKE A VIEW FOR THE ABOVE TABLE, WHERE THE VIEW LOOKS LIKE

PROD_CD GRADE WIDTH THICK LENGTH ANGLE
----------------------------------------------------
A G 200 1 300 30
B C 250 1.5 500 60
C D 300 1.25 550 0

CREATE TABLE PRODUCT_DIMENSIONS
( PROD_CD VARCHAR2(25),
CRITERIA VARCHAR2(20),
CRITERIA_VAL VARCCHAR2(10)
);

INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('A','GRADE','G');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('A','WIDTH','200');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('A','THICK','1');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('A','LENGTH','300');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('A','ANGLE','30');

INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('B','GRADE','C');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('B','WIDTH','250');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('B','THICK','1.5');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('B','LENGTH','500');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('B','ANGLE','60');

INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('C','GRADE','D');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('C','WIDTH','300');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('C','THICK','1.25');
INSERT INTO PRODUCT_DIMENSIONS (PROD_CD,CRITERIA,CRITERIA_VAL) VALUES ('C','LENGTH','550');
Re: How to make a view with rows as columns [message #403575 is a reply to message #403570] Sun, 17 May 2009 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a usual "pivot" or "row to columns" question that is asked at least once per week.
Please search BEFORE posting.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to make a view with rows as columns [message #403576 is a reply to message #403575] Sun, 17 May 2009 03:28 Go to previous messageGo to next message
sanjeev_thoppil
Messages: 4
Registered: May 2009
Junior Member
Yes Michel ,I read the pivot column documents, but in all they use the aggregate function count to get the value. In my example, its not any aggregate function but the value of the pivoted column is a column ie. CRITERIA_VAL. Also the columns is not predefined. Please can u clear/suggest me.
Re: How to make a view with rows as columns [message #403582 is a reply to message #403576] Sun, 17 May 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but in all they use the aggregate function count to get the value.

You missread them.

Quote:
Also the columns is not predefined.

So it is not possible. How a view can have not predifined columns? It is part of its definition like table.

Regards
Michel
Re: How to make a view with rows as columns [message #403583 is a reply to message #403582] Sun, 17 May 2009 04:01 Go to previous messageGo to next message
sanjeev_thoppil
Messages: 4
Registered: May 2009
Junior Member
Michel, what if i restrict the user that u can have only 5 columns, say the criteria is defined, but how do i get the value? In all the examples given for pivot, either they have used max or count.
Re: How to make a view with rows as columns [message #403585 is a reply to message #403583] Sun, 17 May 2009 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you see and what you tried.
I almost NEVER used count and max for pivot statement.

Regards
Michel

[Updated on: Sun, 17 May 2009 04:49]

Report message to a moderator

Re: How to make a view with rows as columns [message #403708 is a reply to message #403570] Mon, 18 May 2009 03:27 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
This reminded me this thread on AskTom: Query on design.
To show value different than NULL for non-present row, use NVL or COALESCE function.
And enjoy its performance for large PRODUCT_DIMENSIONS table...
Previous Topic: Not All Variable Bound (merged 3)
Next Topic: 2 foreign keys reference
Goto Forum:
  


Current Time: Wed Dec 07 22:39:46 CST 2016

Total time taken to generate the page: 0.28685 seconds