CODEWITHSUNDEEP
oracle-database
Americus
Americus

Reputation: 1407

Oracle - dynamic column name in select statement

Question:

Is it possible to have a column name in a select statement changed based on a value in it's result set?

For example, if a year value in a result set is less than 1950, name the column OldYear, otherwise name the column NewYear. The year value in the result set is guaranteed to be the same for all records.

I'm thinking this is impossible, but here was my failed attempt to test the idea:

select 1 as  
(case  
when 2 = 1 then "name1";  
when 1 = 1 then "name2")  
from dual;

Upvotes: 8

Views: 81786

Answers (5)

JKimmel
JKimmel

Reputation: 1

This solution requires that you launch SQLPLUS and a .sql file from a .bat file or using some other method with the appropriate Oracle credentials. The .bat file can be kicked off manually, from a server scheduled task, Control-M job, etc...

Output is a .csv file. This also requires that you replace all commas in the output with some other character or risk column/data mismatch in the output.

The trick is that your column headers and data are selected in two different SELECT statements.

It isn't perfect, but it does work, and it's the closest to standard Oracle SQL that I've found for a dynamic column header outside of a development environment. We use this extensively to generate recurring daily/weekly/monthly reports to users without resorting to a GUI. Output is saved to a shared network drive directory/Sharepoint.

        REM BEGIN runExtract1.bat file -----------------------------------------
        sqlplus username/password@database @C:\DailyExtracts\Extract1.sql > C:\DailyExtracts\Extract1.log

        exit
        REM END runExtract1.bat file -------------------------------------------

        REM BEGIN Extract1.sql file --------------------------------------------
        set colsep ,
        set pagesize 0
        set trimspool on
        set linesize 4000

        column dt new_val X 
        select to_char(sysdate,'MON-YYYY') dt from dual; 

        spool c:\DailyExtracts\&X._Extract1.csv

        select '&X-Project_id', 'datacolumn2-Project_Name', 'datacolumn3-Plant_id' from dual;

        select 
          PROJ_ID
          ||','||
          replace(PROJ_NAME,',',';')--  "Project Name"
          ||','||
          PLANT_ID--  "Plant ID"
         from PROJECTS
         where ADDED_DATE >= TO_DATE('01-'||(select to_char(sysdate,'MON-YYYY') from dual));

        spool off
        exit
        /
        REM ------------------------------------------------------------------


CSV OUTPUT (opened in Excel and copy/pasted):

old   1: select '&X-Project_id'  'datacolumn2-Project_Name'  'datacolumn3-Plant_id' from dual
new   1: select 'MAR-2018-Project_id'    'datacolumn2-Project_Name'  'datacolumn3-Plant_id' from dual
MAR-2018-Project_id datacolumn2-Project_Name    datacolumn3-Plant_id

    31415   name1   1007
    31415   name1   2032
    32123   name2   3302
    32123   name2   3384
    32963   name3   2530
    33629   name4   1161
    34180   name5   1173
    34180   name5   1205
    ...
    ...
    etc...

    135 rows selected.

Upvotes: 0

Thought
Thought

Reputation: 710

The "correct" way to do this in SQL is to have both columns, and have the column that is inappropriate be NULL, such as:

SELECT
   CASE WHEN year < 1950 THEN year ELSE NULL END AS OldYear,
   CASE WHEN year >= 1950 THEN year ELSE NULL END AS NewYear
FROM some_table_with_years;

Upvotes: 4

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

There is no good reason to change the column name dynamically - it's analogous to the name of a variable in procedural code - it's just a label that you might refer to later in your code, so you don't want it to change at runtime.

I'm guessing what you're really after is a way to format the output (e.g. for printing in a report) differently depending on the data. In that case I would generate the heading text as a separate column in the query, e.g.:

SELECT 1 AS mydata
      ,case
          when 2 = 1 then 'name1'
          when 1 = 1 then 'name2'
       end AS myheader
FROM dual;

Then the calling procedure would take the values returned for mydata and myheader and format them for output as required.

Upvotes: 2

Irfan Mulic
Irfan Mulic

Reputation: 1206

You will need something similar to this: 

select 'select ' || CASE WHEN YEAR<1950 THEN 'OLDYEAR' ELSE 'NEWYEAR' END  || ' FROM TABLE 1' from TABLE_WITH_DATA

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562951

You can't vary a column name per row of a result set. This is basic to relational databases. The names of columns are part of the table "header" and a name applies to the column under it for all rows.

Re comment: OK, maybe the OP Americus means that the result is known to be exactly one row. But regardless, SQL has no syntax to support a dynamic column alias. Column aliases must be constant in a query.

Even dynamic SQL doesn't help, because you'd have to run the query twice. Once to get the value, and a second time to re-run the query with a different column alias.

Upvotes: 5

Related Questions