Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic column in SQL Output (Oracle 11.2.0.2)
Dynamic column in SQL Output [message #598446] Tue, 15 October 2013 01:09 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Dear friends,

I need to have columns dynamically defined in my SQL based on the column values in the table.

Selct emp_no,dept_no,grade from emp;
emp_no       dept_no      grade
----------------------------------
1            10           A
2            20           B
3            20           A
4            10           C
5            10           A
6            20           C
7            10           B
8            20           C


I need the output as
dept_no     Count_A      Count_B     Count_C
---------------------------------------------
10            2            1            1
20            1            1            2


I can write hard coded sql to get this output but I need the count columns to be generated dynamically as there could be new entries in the grade column later. For example if there is a entry as D for the grade column my SQL should have a column count_D. Any idea how can I achieve this?
Re: Dynamic column in SQL Output [message #598447 is a reply to message #598446] Tue, 15 October 2013 01:23 Go to previous messageGo to next message
Maaher
Messages: 7054
Registered: December 2001
Senior Member
Short answer: you can't. Not in simple SQL. Tom Kyte, however, does answer this particular question pretty well here.

MHE

Edit: out of curiosity, what would you do with a query like that? You can't build a report with it, since the columns might/will change over time, invalidating the report.

[Updated on: Tue, 15 October 2013 01:25]

Report message to a moderator

Re: Dynamic column in SQL Output [message #598449 is a reply to message #598447] Tue, 15 October 2013 01:41 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
@Maaher

Thanks, Infact I need this for a report.
icon14.gif  Re: Dynamic column in SQL Output [message #598450 is a reply to message #598446] Tue, 15 October 2013 01:43 Go to previous message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can read the questions and answers at:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:595725000346503967
http://www.orafaq.com/forum/t/168509/102589/
http://www.orafaq.com/forum/t/168264/102589/
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:124812348063#51542758465483
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:52266643928180#52284733269998

Previous Topic: Error ORA-04091
Next Topic: file input and output
Goto Forum:
  


Current Time: Sat Oct 25 02:34:09 CDT 2014

Total time taken to generate the page: 0.13388 seconds