OraFAQ Forum: SQL & PL/SQL » Dynamic column in SQL Output

Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic column in SQL Output (Oracle 11.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamic column in SQL Output [message #598446]

Tue, 15 October 2013 01:09

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?

Report message to a moderator

Re: Dynamic column in SQL Output [message #598447 is a reply to message #598446]

Tue, 15 October 2013 01:23

Maaher
Messages: 7065
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