3 Replies Latest reply: Oct 13, 2010 11:44 AM by Frank Kulash RSS

    Dynamic Column Headers

    525708
      Dear Members,

      I have a sql as below:
      select msi.segment1,
              sum(CASE WHEN mdh.period_start_date > trunc(add_months(sysdate,-1))
                             THEN mdh.sales_order_demand ELSE 0 END) MONTH_USAGE_1
      from mtl_demand_histories mdh,
           mtl_system_items msi
      where msi.inventory_item_id = mdh.inventory_item_id and
            msi.organization_id = mdh.organization_id
      group by segment1
      From the above sql the 2nd column name(MONTH_USAGE_1) should be dynamic and the name can be derived from the following:

      to_char(trunc(add_months(sysdate,-1)),'MON-RRRR') which will result in SEP-2010.

      So the 2nd column name should be SEP-2010.

      How can i do this in SQL.

      Thanks
      Sandeep
        • 1. Re: Dynamic Column Headers
          JustinCave
          If you really need to do that, you'd need to generate the SQL statement dynamically. That would also mean that any code that tries to fetch the data from the cursor created from this SQL statement would need to know how to fetch data without knowing the names of the columns in the result set at compile time. If you're using PL/SQL to fetch the data, that implies that you'd have to use dynamic SQL to fetch the data as well.

          Generally, this is not something I would recommend trying to do. It's an awful lot of complexity to add to the system for a minor cosmetic benefit.

          Justin
          • 2. Re: Dynamic Column Headers
            HG75
            • 3. Re: Dynamic Column Headers
              Frank Kulash
              Hi, Sandeep,

              As Justin said, the column name must be hard-coded into the query. Hard-codeing a duynamic value requires dynamic SQL.
              If you're using SQL*Plus, which has substitution variables, then it's not very hard.
              --  Define substitution variable last_month
              
              COLUMN     last_month_col     NEW_VALUE last_month
              
              SELECT  TO_CHAR ( ADD_MONTHS (SYSDATE, -1)
                        , 'MON-YYYY'
                        )          AS last_month_col
              FROM    dual;
              
              -- Last_month can now be used anywhere in the query:
              
              select msi.segment1,
                      sum(CASE WHEN mdh.period_start_date > trunc(add_months(sysdate,-1))
                                     THEN mdh.sales_order_demand ELSE 0 END)          AS "&last_month"
              from   ... 
              Using the SQL*Plus COLUMN ... HEADING command, you can have SQL*Plus display another heading in place of the one hard-coded into the query.
              Technically, this is not dynamic SQL, but it's slightly more work:
              COLUMN     last_month_col     NEW_VALUE last_month
               
              SELECT     deptno
              ,     dname     AS "&last_month"
              FROM     scott.dept;
              
              COLUMN  month_usage_1     HEADING "&last_month"
              
              select msi.segment1,
                      sum(CASE WHEN mdh.period_start_date > trunc(add_months(sysdate,-1))
                                     THEN mdh.sales_order_demand ELSE 0 END)          AS month_usage_1
              from   ...