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!

conditonal column labels?

Discussion in 'SQL PL/SQL' started by nazdev, Mar 16, 2011.

  1. nazdev

    nazdev Guest

    I have a question about using a select statement.

    I have a standard select:

    select jan, feb, mar, apr, ytd from ea_reports.

    what I'd like to do is have the labels for my output be dynamic based on a variable. that is something like this:

    select jan as (if :p11_MQY = 'M' then 'Jan', else '1QTR'),
    feb as (if :p11_MQY = 'M' then 'Feb', else '2QTR'),
    mar as (if :p11_MQY = 'M' then 'Mar', else '3QTR'),
    apr as (if :p11_MQY = 'M' then 'Apr', else '4QTR'),
    ytd from ea_reports

    I know this is not correct syntax, but I've tried decode and case and can't get them to work. so I'm wondering is there a way to do it that the experts might know? or perhaps my use of decode was just wrong.. I could use some advice.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Show what you wrote, and we can correct the errors or suggest alternatives.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is no straight SQL for making dynamic column header. You have to generate sql by spooling and execute it.

    Here is a hint thta you can try.


    Code (SQL):
    SQL> WITH REC AS (SELECT 'N' FLG FROM DUAL)
      2  SELECT 'SELECT ENAME AS "'||CASE WHEN REC.FLG='O' THEN 'OLDNAME' ELSE 'NEWN
    AME'
    END||'" FROM EMP;'
      3  SQL FROM DUAL,REC;

    SQL
    ----------------------------------------
    SELECT ENAME AS "NEWNAME" FROM EMP;

    SQL> WITH REC AS (SELECT 'O' FLG FROM DUAL)
      2  SELECT 'SELECT ENAME AS "'||CASE WHEN REC.FLG='O' THEN 'OLDNAME' ELSE 'NEWN
    AME'
    END||'" FROM EMP;'
      3  SQL FROM DUAL,REC;

    SQL
    ----------------------------------------
    SELECT ENAME AS "OLDNAME" FROM EMP;

    SQL>