Home » SQL & PL/SQL » SQL & PL/SQL » cross tab query problem
cross tab query problem [message #262243] Sat, 25 August 2007 10:29 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member

I have a row as below

select ename,hiredate,sal from emp where empno=10;

ename                hiredate                sal
-------------------------------------------------
WARD                 12-OCT-2005             3000

my output should be

WARD   
12-OCT-2005
3000

thanks

Re: cross tab query problem [message #262245 is a reply to message #262243] Sat, 25 August 2007 11:07 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First, you shall generate extra rows as described eg. in Oracle Row Generator Techniques article.
Then use DECODE function to get the different column value for each generated row.
Take care of implicit datatype conversion (use TO_CHAR for numeric/date column values).
Re: cross tab query problem [message #262248 is a reply to message #262243] Sat, 25 August 2007 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is just output:
SQL> set lines 20
SQL> select ename,hiredate,sal from emp where rownum=1
  2  /
ENAME
----------
HIREDATE
-------------------
       SAL
----------
SMITH
17/12/1980 00:00:00
       800

1 row selected.

Regards
Michel
Re: cross tab query problem [message #262297 is a reply to message #262243] Sun, 26 August 2007 01:25 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
Hi,
tried with the above solutions,but not the correct output.
any help appreciated
thanks
Re: cross tab query problem [message #262309 is a reply to message #262297] Sun, 26 August 2007 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post (copy and paste) what you tried we can't know it.

Regards
Michel
Re: cross tab query problem [message #262314 is a reply to message #262297] Sun, 26 August 2007 02:18 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Are you wanting to create a row from each column? If so, you can accomplish this with a couple of UNION operators.
WITH x AS ( SELECT ename, TO_CHAR(hiredate) hiredate, TO_CHAR(sal) sal
         FROM   emp
         WHERE  ROWNUM <= 1 )
SELECT ename col1 FROM x
UNION
SELECT hiredate FROM x
UNION
SELECT sal FROM x

COL1
--------------
17-DEC-1980
800
SMITH

Re: cross tab query problem [message #262316 is a reply to message #262314] Sun, 26 August 2007 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let the OP explains his problem.
Give him a solution will not help, it just turns him to a dependent person.
Moreover you give a solution but you don't know what is really the question. Wait for it.

In addition, is this a really good solution? Three accesses to the table...

Regards
Michel

[Updated on: Sun, 26 August 2007 02:33]

Report message to a moderator

Re: cross tab query problem [message #262319 is a reply to message #262316] Sun, 26 August 2007 02:48 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Michel, were you not held enough as a child or something...oh nevermind
Quote:

If it is just output:
...


Hmmmm...looks like a previous poster wasn't sure of the problem yet posted a possible solution. Oh wait, that was you!
Quote:

In addition, is this a really good solution? Three accesses to the table...


Dunno, isn't that the purpose of this site - to discuss the issues and come up with alternative solutions.

Re: cross tab query problem [message #262321 is a reply to message #262319] Sun, 26 August 2007 02:53 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Just for fun, here's another possible solution:

SELECT DECODE(LEVEL, 1, ename, 2, hiredate, 3, sal) col1
FROM ( SELECT ename, hiredate, sal
       FROM emp
       WHERE ROWNUM <= 1 ) x
CONNECT BY ROWNUM <= 3;

COL1
--------------
SMITH
17-DEC-1980
800

Re: cross tab query problem [message #262326 is a reply to message #262319] Sun, 26 August 2007 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
were you not held enough as a child or something

Sorry english is not my first language and I don't understand this expression.
Quote:
posted a possible solution

This was not a solution, of course not, this was just to show another point of view.
Quote:
Dunno, isn't that the purpose of this site - to discuss the issues and come up with alternative solutions.

Yes, but there must be a clearly defined problem before.
Otherwise we can endlessly discuss about angel sex (I don't know if this can be said in english).
Quote:
here's another possible solution

This is not a solution as there is no defined problem.
If you want to discuss about different solutions about the point you have in mind, open a new topic. I'm sure it will have a great success, I can already imagine several "solutions" and be sure we will find than a dozen.

Regards
Michel

[Updated on: Sun, 26 August 2007 03:25]

Report message to a moderator

Re: cross tab query problem [message #262350 is a reply to message #262326] Sun, 26 August 2007 07:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel,
any member of the forum is entitled to post whatever (s)he thinks helps the OP, as long as it doesn't offend anyone.
There is no point in (trying to) 'forbidding' people to post, until _you_ think you know enough about the problem. If one would advise cmerry to hold back to not spoonfeed the OP, that's ok; any comments because he posts the same solution flyboy described, very well.
But don't attack him for posting a possible solution; especially since you did exactly the same in your first reply..
Re: cross tab query problem [message #262358 is a reply to message #262350] Sun, 26 August 2007 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
especially since you did exactly the same in your first reply..

I already answered for this. I precise. I thought to just post a link to linesize but realized this was too cryptic so post an example. Once again the purpose was NOT to give a solution just another point a view, just to point the OP the question was not clear.
Quote:
There is no point in (trying to) 'forbidding' people to post

I didn't forbid anything I post my opinion. Do you (try to) 'forbid' me to do it? Wink

Regards
Michel

Re: cross tab query problem [message #262371 is a reply to message #262350] Sun, 26 August 2007 11:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, rereading this, maybe I've been a little bit too far this time.
I'm waiting for Barbara to lecture me.
./fa/1683/0/

Regards
Michel
Previous Topic: plsql pb
Next Topic: Help Needed
Goto Forum:
  


Current Time: Sun Dec 04 16:37:22 CST 2016

Total time taken to generate the page: 0.08120 seconds