Home » SQL & PL/SQL » SQL & PL/SQL » oracle view - urgent (oracle 9i,windows)
icon5.gif  oracle view - urgent [message #357289] Tue, 04 November 2008 11:36 Go to next message
deb.simply
Messages: 5
Registered: August 2008
Junior Member
hi every body

i have a problem and hope some body help me.

Q- suppose EMP table is there and the columns are - ename,empid etc. In ename column some data is there like x,y,x etc

ex - select ename from emp;
ENAME
x
y
z

Now i want to create a view which will display it like

X Y Z (three different columns)

Is it possible in view ?
Re: oracle view - urgent [message #357291 is a reply to message #357289] Tue, 04 November 2008 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is urgent is:
1/ Read OraFAQ Forum Guide
2/ Don't use "urgent"
3/ Format your post
4/ Post your Oracle version
5/ Post a test case
6/ Post what you already tried

And above all:
7/ SEARCH BEFORE posting.

Regards
Michel
Re: oracle view - urgent [message #357309 is a reply to message #357289] Tue, 04 November 2008 17:31 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi, will this help you? If not reply me back.

DECLARE
CURSOR C_EMP IS SELECT ENAME FROM EMP;
C_ENAME EMP.ENAME%TYPE;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO C_ENAME;
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT(' '||C_ENAME);
END LOOP;
dbms_output.put_line(' ');
CLOSE C_EMP;
END;
Re: oracle view - urgent [message #357314 is a reply to message #357309] Tue, 04 November 2008 19:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
A view will not help you unless you can live with a predefined number of columns. Views cannot be defined with a dynamic number of columns. Unless you know the maximum number of ename values in your example, you cannot create a view to do what you want.

On an aside, what you have asked for is to do a data pivot. Read up about it for ideas on how to do the pivoting.

Kevin
Re: oracle view - urgent [message #357462 is a reply to message #357309] Wed, 05 November 2008 07:31 Go to previous messageGo to next message
deb.simply
Messages: 5
Registered: August 2008
Junior Member
hi Lakshmis,

thanks for your reply,
your code is working but, i want to know is it possible in VIEW ?


Re: oracle view - urgent [message #357463 is a reply to message #357462] Wed, 05 November 2008 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kevin Meade wrote on Wed, 05 November 2008 02:32
A view will not help you unless you can live with a predefined number of columns. Views cannot be defined with a dynamic number of columns. Unless you know the maximum number of ename values in your example, you cannot create a view to do what you want.

On an aside, what you have asked for is to do a data pivot. Read up about it for ideas on how to do the pivoting.

Kevin

Did you read that post and especially did you follow the last sentence?

Regards
Michel
Re: oracle view - urgent [message #357478 is a reply to message #357463] Wed, 05 November 2008 10:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I read the post.

To which last sentence are you referring.

Kevin
Re: oracle view - urgent [message #357479 is a reply to message #357478] Wed, 05 November 2008 10:06 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kevin, I answered to "deb.simply"'s last post quoting and reminding him yours.

Regards
Michel
Previous Topic: Oracle newbie
Next Topic: Optimized query does not uses same plan if used to create a table
Goto Forum:
  


Current Time: Thu Dec 08 20:31:10 CST 2016

Total time taken to generate the page: 0.05251 seconds