Oracle - dynamic column name in select statement

Question

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;

Answer 1

You probably need to use dynamic SQL to accomplish this.

Answer 2

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.

Answer 3

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

Answer 4

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.