CODEWITHSUNDEEP
sqloracle-database
John OReilly
John OReilly

Reputation: 13

I want to dynamically change resultset column names

I'm trying to change column names in resultset, dynamically adding the year from sysdate.

So I've tried something like this:

create table TestTable (
      name varchar2(30),
      age  INTEGER
);

insert into TestTable values('johnny1',10);
insert into TestTable values('johnny2',15);

Select qry0.name,qry0.age as to_char("age" || qry1.theyear)
from (Select name,age from TestTable) qry0,
     (select to_char(sysdate,('YYYY'))as TheYear from dual)qry1;

I trying to get column headers like this:

NAME    AGE2019

Thanks

Upvotes: 1

Views: 57

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

If you are using something like SQLPlus to generate your report, and you are just looking an appropriate name in your report, you could use a substitution variable, eg

SQL> create table TestTable (
  2        name varchar2(30),
  3        age  INTEGER
  4  );

Table created.

SQL>
SQL> insert into TestTable values('johnny1',10);

1 row created.

SQL> insert into TestTable values('johnny2',15);

1 row created.

SQL> col xx new_value yy
SQL> select 'AGE'||to_char(sysdate,'YYYY') xx from dual;

XX
-------
AGE2019

SQL> select name, age &&yy from testtable;

NAME                              AGE2019
------------------------------ ----------
johnny1                                10
johnny2                                15

Upvotes: 3

Related Questions