Oracle - dynamic column name in select statement

Question

Question:

Is it possible for the column name in the select expression to change based on the value of the result set in it?

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

I think this is not possible, but here is my unsuccessful attempt to test the idea:

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

Answer 1

You cannot change the name of a column in a row in a result set. This is the basic database of relational databases. Column names are part of the header table, and the name is applied to the column below it for all rows.

---

Re comment: OK, perhaps OP Americus means that the result is known as exactly one line. But independently, SQL does not have syntax to support dynamic column alias. Column aliases must be constant in the query.

Even dynamic SQL does not help, because you have to run the query twice. One time to get the value, and a second time to re-run the query with a different column alias.

Answer 2

The “right” way to do this in SQL is to have both columns and have an invalid NULL column, for example:

 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; 

Answer 3

You will need something like this:

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

Answer 4

There is no reason to change the column name dynamically - it is similar to the variable name in the procedural code - it is just a label that you can reference later in your code, so you do not want it to change at runtime.

I assume that you are actually after this - a way to format the output (for example, to print in a report) differently depending on the data. In this case, I would generate the header text as a separate column in the query, for example:

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

The calling procedure then takes the values ​​returned for mydata and myheader, and formats them for output as necessary.