1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dynamic program to generate Columns

Discussion in 'SQL PL/SQL' started by Bharat, Nov 24, 2014.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    Is there any way to generate columns dynamically ?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Explain, what you try to make?

    The decision is necessary in sql or pl/sql?
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    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
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    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.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    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?
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Bharat,

    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:

    http://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

    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:
    http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_QUESTION_ID:2196162600402
     
    Bharat likes this.
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I'm not sure that pivot is the answer to this one since we do not know what, exactly, is expected.
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    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.
     
    Bharat likes this.
  10. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Matthew,

    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.
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Bharat,

    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')

    Code (SQL):
    DECLARE
      l_count    NUMBER := 0;
      l_salesman VARCHAR2(30);
    BEGIN
      FOR C IN
      ( WITH sales AS
      ( SELECT 'Tom' salesman, DATE '2014-05-20' sdate, 2000 amount FROM dual
      UNION ALL
      SELECT 'Tom' , DATE '2014-06-05' , 2500 FROM dual
      UNION ALL
      SELECT 'Tom' , DATE '2014-06-20' , 500 FROM dual
      UNION ALL
      SELECT 'Jerry' , DATE '2014-04-20' , 200 FROM dual
      UNION ALL
      SELECT 'Jerry' , DATE '2014-07-05' , 500 FROM dual
      UNION ALL
      SELECT 'Dick' , DATE '2014-08-08' , 100 FROM dual
      UNION ALL
      SELECT 'Dick' , DATE '2014-08-28' , 5000 FROM dual
      UNION ALL
      SELECT 'Harry' , DATE '2014-06-10' , 2500 FROM dual
      UNION ALL
      SELECT 'Harry' , DATE '2014-10-12' , 200 FROM dual
      UNION ALL
      SELECT 'Harry' , DATE '2014-11-15' , 200 FROM dual
      ),
      periods AS
      (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
      FROM dual
        CONNECT BY level <= months_between(last_day(to_date(:p_to, 'Mon-YY'))+1, to_date(:p_from, 'Mon-YY') )
      ORDER BY mon
      )
    SELECT salesman,
      p.per,
      p.mon,
      SUM(
      CASE
        WHEN p.per = TO_CHAR(s.sdate, 'Mon-YY')
        THEN amount
        ELSE 0
      END ) total
    FROM sales s,
      periods p
    GROUP BY salesman,
      p.mon,
      p.per
    ORDER BY salesman,
      p.mon
      )
      LOOP
        l_count      := l_count + 1;
        IF l_count    = 1 THEN
          l_salesman := rpad(c.salesman, 15);
        ELSE
          l_salesman := ' ' ;
        END IF;
        dbms_output.put (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
          dbms_output.put_line('');
          l_count := 0;
        END IF;
      END LOOP;
    END;
    /