Oracle - Table function that returns dynamic column names

Question

I'm building a report that should generate a dynamic list of columns based on the requested years, i.e. 2000 to 2002. The user should be able to run the report via a SELECT statement like so:

SELECT * FROM TABLE(myreport(2002, 3)); -- Get 3 report years starting with 2002

Currently, I have a table function that generates this output, but with static column names:

-- Row type
CREATE OR REPLACE TYPE typ_myreport AS OBJECT (
  myuser VARCHAR(260),
  yr1_p DECIMAL(5,2),
  yr1_t INTEGER,
  yr2_p DECIMAL(5,2),
  yr2_t INTEGER,
  ...
);

-- Table type
CREATE OR REPLACE TYPE tab_myreport AS TABLE OF typ_myreport;

-- Report
CREATE OR REPLACE FUNCTION myreport(pAsOfYear IN INTEGER) 
RETURN tab_myreport PIPELINED AS
BEGIN
  FOR vRec IN (
    SELECT user_name, yr1_p, yr1_t, y2_p, y2_t, ...
    FROM mytable
  ) 
  LOOP
    PIPE ROW (
      typ_myreport(vRec.user_name, vRec.yr1_p, vRec.yr1_t, vRec.yr2_p, vRec.year2_t, ...)
    );
  END LOOP;

  RETURN;
END;

It produces output like this:

User    yr1_p   yr1_t   yr2_p   yr2_t   yr3_p   yr3_t
-----------------------------------------------------
Bobby      25       2      33       2      20       4
Barry      50       4      66       4      50      10
Big Ben    25       2       0       0      30       6

But I want the columns to be dynamically named based on the requested years:

User    2000_p  2000_t  2001_p  2001_t  2002_p  2002_t
------------------------------------------------------
Bobby       25       2      33       2      20       4
Barry       50       4      66       4      50      10
Big Ben     25       2       0       0      30       6

The output result set should remain the exact same -- table structure, data types -- just the column names should change. My thought was to do this via a dynamically built SELECT statement, which I could execute and then return the results. I've fumbled around trying to get it to work, but no luck.

Is there a way to write a dynamic SQL in a function / procedure with a dynamic number of columns and column names and then return that result set as a table (not script) output?

Update
Here is a simplified snippet of the actual query that generates the yr fields:

WITH rpt_years AS (SELECT * FROM TABLE(GetReportyears(pAsOfYear)))
SELECT user_name,
  SUM(year1_cnt) AS yr1_t,
  SUM(COALESCE((year1_cnt / NULLIF(year1_tot,0)),0)) AS yr1_p,
  SUM(year2_cnt) AS yr2_t, 
  SUM(COALESCE((year2_cnt / NULLIF(year2_tot,0)),0)) AS yr2_p,
  ...
FROM (
  SELECT DISTINCT 
    ui.user_name, 
    SUM(CASE WHEN s.dist_year = y.year1 THEN 1 END) AS year1_cnt,
    COUNT(CASE WHEN s.dist_year = y.year1 THEN 1 END) AS year1_tot,
    SUM(CASE WHEN s.dist_year = y.year2 THEN 1 END) AS year2_cnt,
    COUNT(CASE WHEN s.dist_year = y.year2 THEN 1 END) AS year2_tot,
    ...
  FROM rpt_years y -- Get report years as single row
  INNER JOIN src_table s
  ...
) src
ORDER BY src.user_name

Answer 1

You can use the open source program Method4 to return a dynamic number of columns in a SQL context.

To build exactly what you want, everything in a single line of code, would require creating a new type. Since these types are complex, I recommend saving that task for later. For the first step, if you only use the existing types, you only need to write a SQL statement that generates the correct SQL statement.

For an example, let's use this table to represent the results of your existing, large query:

create table MyTable as
select 'Bobby'   user_name, 25 yr1_p, 2 yr1_t, 33 yr2_p, 2 yr2_t, 20 yr3_p, 4  yr3_t from dual union all
select 'Barry'   user_name, 50 yr1_p, 4 yr1_t, 66 yr2_p, 4 yr2_t, 50 yr3_p, 10 yr3_t from dual union all
select 'Big Ben' user_name, 25 yr1_p, 2 yr1_t,  0 yr2_p, 0 yr2_t, 30 yr3_p,  6 yr3_t from dual;

The below static query will convert the column names into the format you want:

select user_name, yr1_p "2000_P", yr1_t "2000_T", yr2_p "2001_P", yr2_t "2001_T", yr3_p "2002_P", yr3_t "2002_T"
from myTable;

You can generate the above query with the below query:

select
    'select user_name, ' ||
    listagg('yr'||level||'_p "'||(start_year + level - 1)||'_P", yr'||level||'_t "'||(start_year + level - 1)||'_T"', ', ') within group (order by start_year) || 
    ' from myTable' v_sql
from
(
    select 2000 start_year, 3 number_of_years from dual
)
connect by level <= number_of_years;

The next query puts it all together. After installing Method4, The DYNAMIC_QUERY function can run the query generated by another query, producing the results and column names you want. You only need to change the values 2000 and 3 to adjust the results.

select * from table(method4.dynamic_query(q'[
    select
        'select user_name, ' ||
        listagg('yr'||level||'_p "'||(start_year + level - 1)||'_P", yr'||level||'_t "'||(start_year + level - 1)||'_T"', ', ') within group (order by start_year) || 
        ' from myTable' v_sql
    from
    (
        --Only change the values in here:
        select 2000 start_year, 3 number_of_years from dual
    )
    connect by level <= number_of_years
]'));

You'll also need to replace myTable with your query that generates the intermediate results. This isn't exactly what you want - although users will only need to change two numbers, they will have to copy and paste a ginormous query.

If you can't live with the copy and pasting, there's still a way to create a solution that only needs a one line SQL statement. For that solution, you'll need to create your own type and add it to the package specification. Most of the work would just be copying the files method4_dynamic_ot.tpb and method4_dynamic_ot.tps, changing names and parameters, modifying the function re_evaluate_statement, and then adding the new type to the package specification. I could help with that, but before working on that advanced solution you should check to make sure the DYAMIC_QUERY approach works first.