Discussion in 'SQL PL/SQL' started by Bharat, Nov 24, 2014.
Is there any way to generate columns dynamically ?
Explain, what you try to make?
The decision is necessary in sql or pl/sql?
We have an requirement like users will pass a parameter called Number of months. Based on that we need to provide those many columns of data from current month.
If we select parameter as 2 then it has to show Column-1 as Nov-14 and Column-2 as Dec-14
If we select parameter as 12 then it has to show Column-1 as Nov-14, Column-2 as Dec-14, ... Column-12 as Oct-15
You need to post some code showing what it is you're wanting to return, create table statements and sample data. We can't do much with what you've posted so far.
Version oracle ?
For receiving result in the form of the table it is possible to use ref_cursor or anydataset.....
The result in what look is necessary: line or table,collection?
It looks like you have a case for a "pivot" solution.
If you are on 11g (that's why I suppose Sergey asked for your Oracle version), there is a built-in PIVOT operator which might do the job - have a look at the following link:
If not (on 11g), then you'll find plenty of articles describing how to do it and in the absence of any table description and data (as David pointed out), it's difficult to propose something more precise.
Some interesting articles' links which you may try:
I'm not sure that pivot is the answer to this one since we do not know what, exactly, is expected.
Additional links :
From OP's post, the text "...pass a parameter..." seems to indicate you are referring to PL/SQL. Hopefully you are because this would be considerably more difficult using SQL. For a better answer, we need to know what you mean by "...we need to provide'''
Specifically, what are you referring to by 'provide'? Are you creating a report? If so -- is it onscreen? Emailed? Saved to a file? If not -- what exactly is being provided to your users?
I can think of several ways to dynamically alter the number of columns created for a report using PL/SQL. I have no idea if any of them are applicable to your situation.
Yes, we want to provide the output in the pl/sql report. Based on the user inputs (Two Dates: From and To Dates) we need to split the dates into buckets month wise and have to display the results in column wise.
Here's a short and quick piece of code you can build up on. I've used basic sample Sales data as an example.
Input parameters: p_from & p_to (Assumption: type: Varchar2, format: 'Mon-YY')
l_count NUMBER := 0;
FOR C IN
( WITH sales AS
( SELECT 'Tom' salesman, DATE '2014-05-20' sdate, 2000 amount FROM dual
SELECT 'Tom' , DATE '2014-06-05' , 2500 FROM dual
SELECT 'Tom' , DATE '2014-06-20' , 500 FROM dual
SELECT 'Jerry' , DATE '2014-04-20' , 200 FROM dual
SELECT 'Jerry' , DATE '2014-07-05' , 500 FROM dual
SELECT 'Dick' , DATE '2014-08-08' , 100 FROM dual
SELECT 'Dick' , DATE '2014-08-28' , 5000 FROM dual
SELECT 'Harry' , DATE '2014-06-10' , 2500 FROM dual
SELECT 'Harry' , DATE '2014-10-12' , 200 FROM dual
SELECT 'Harry' , DATE '2014-11-15' , 200 FROM dual
(SELECT TO_CHAR(ADD_MONTHS(to_date(:p_from, 'Mon-YY'), level-1),'Mon-YY') per,
ADD_MONTHS(to_date(:p_from, 'Mon-YY'), level -1) mon
CONNECT BY level <= months_between(last_day(to_date(:p_to, 'Mon-YY'))+1, to_date(:p_from, 'Mon-YY') )
ORDER BY mon
WHEN p.per = TO_CHAR(s.sdate, 'Mon-YY')
END ) total
FROM sales s,
GROUP BY salesman,
ORDER BY salesman,
l_count := l_count + 1;
IF l_count = 1 THEN
l_salesman := rpad(c.salesman, 15);
l_salesman := ' ' ;
dbms_output.put (lpad(c.total, 15));
IF l_count = months_between(last_day(to_date(:p_to, 'Mon-YY'))+1, to_date(:p_from, 'Mon-YY') ) THEN
l_count := 0;