Dynamic column name in Oracle

Question

I'm trying to dynamically set a column header.

Here the example:

SELECT Name, COUNT(cars) AS (('cars_from_year_') || year)
FROM peaple 
WHERE car = 'GM'
AND Date BETWEEN (year || '0401') AND (year || '0430');

The year should be for ex. 2012 and change (i.e. this being dynamic) for every year (2013,2014,...).

I know the call select to_char(sysdate,'YYYY') from dual but not how to implement it the select statement above?

Solution 1

Column names must be known at compile time. You obviously want to use dynamic column names so you will need to postpone the compilation until the actual execution. There are several ways to do this: DBMS_SQL, EXECUTE IMMEDIATE and REF CURSOR for example.

Here's an example with REF CURSOR and SQL*Plus:

SQL> var x refcursor
SQL> DECLARE
  2     l_year NUMBER := 2012;
  3  BEGIN
  4     OPEN :x
  5        FOR 'SELECT ''This is the year ''||:year AS "Year ' || l_year || '"
  6               FROM DUAL'
  7        USING l_year;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> print x

Year 2012
--------------------------
This is the year 2012

Solution 2

It can be done with crosstab. Take a look this and this for querying crosstab on oracle