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 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 2

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;