Home » SQL & PL/SQL » SQL & PL/SQL » Please Provide me a Solution(do you like my stupid title?)
Please Provide me a Solution(do you like my stupid title?) [message #244737] Thu, 14 June 2007 00:37 Go to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
In the EMP table we have ename and deptno columns.

I want to print department number as column heading and the employees in that particular department as data.
For this i have written the following Query

select decode(deptno,10,ename) as dept10,
decode(deptno,20,ename) as dept20,
decode(deptno,30,ename) as dept30
FROM EMP
ORDER BY 1,2,3

The output of the query is in the attached file.

In the above query i have hardcoded the department numbers. but in the runtime i dont know howmany departments does the table have. so i want to make this as a dynamic one.

so could anyone please tell me how can I accomplish this using SQL only?

[Updated on: Thu, 14 June 2007 07:54] by Moderator

Report message to a moderator

Re: Please Provide me a Solution(Urgent) [message #244747 is a reply to message #244737] Thu, 14 June 2007 00:56 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Provide me a Solution(Urgent)
Please explain to ALL readers why it is urgent for ME to provide answers to YOU.
If you do NOT like any response here you are entitled to a complete, FULL & 100% full refund.
Re: Please Provide me a Solution(Urgent) [message #244750 is a reply to message #244747] Thu, 14 June 2007 01:01 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
Because i need to submit the output to the Reports team and we are using COGNOS as a reporting tool.
Re: Please Provide me a Solution(Urgent) [message #244751 is a reply to message #244737] Thu, 14 June 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please it is urgent to read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

Even when I'm paid urgent is only for production, EMP table is never production, so it can't be urgent.

Moreover, did you make any search? Maybe you already found an answer as this issue has been asked and answered many times.

Regards
Michel
Re: Please Provide me a Solution(Urgent) [message #244755 is a reply to message #244737] Thu, 14 June 2007 01:05 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Thanks Michel.
You are up early & it is time for me to go to bed.
Re: Please Provide me a Solution(Urgent) [message #244762 is a reply to message #244737] Thu, 14 June 2007 01:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ananth.dikshit wrote on Thu, 14 June 2007 07:37
In the above query i have hardcoded the department numbers. but in the runtime i dont know howmany departments does the table have. so i want to make this as a dynamic one.
you cannot. Not in SQL. You *could* make a function returning a ref cursor but then again: the calling environment doesn't know how many columns it'll get. This is not such a brilliant concept. I fail to see the need for such a design.

Apart from that:
- I don't download just any file.
- Read Michel's post.
- Read Michel's post again.

MHE
Re: Please Provide me a Solution(Urgent) [message #244883 is a reply to message #244750] Thu, 14 June 2007 07:56 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ananth.dikshit wrote on Thu, 14 June 2007 02:01
Because i need to submit the output to the Reports team and we are using COGNOS as a reporting tool.


Please forward my fee in advance of $150 per hour to my Swiss bank account and you will have the answer shortly. I will gladly refund any unused portion of the fee when complete.

[Updated on: Thu, 14 June 2007 07:56]

Report message to a moderator

Re: Please Provide me a Solution(do you like my stupid title?) [message #244886 is a reply to message #244737] Thu, 14 June 2007 08:25 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
go to

http://asktom.oracle.com

and search on "pivot query"
Previous Topic: Hints Doc require
Next Topic: PACKED data conversion
Goto Forum:
  


Current Time: Mon Dec 05 11:02:27 CST 2016

Total time taken to generate the page: 0.09807 seconds