I have case with two results sets and i need to merger them into one result set. Have tired multiple times doing various queries to get the required output but fails.
These are the result sets.
select drc.rc_column_name from dyna_report_columns drc;
select * from REPORT1;
what i am trying to do is to get the results from rc_column_name into different columns as a single row on to the next result set.
something like this.
i am planning on dynamizing the report"
You want a solution which can handle any number of columns. So you cannot do this is a regular query. You need to use dynamic SQL to assemble the projection on the fly.
This means a stored procedure or function which executes the dynamic SQL statement and returns a weak ref cursor. Again, it has to be a ref cursor because that is the only construct which can handle a variable projection. Ref cursors are compatible with JDBC ResultSet and ODBC ResultSet, and equivalents in most common front-end programming languages.
Here is a function which uses your posted tables:
create or replace function get_any_report return sys_refcursor
is
stmt varchar2(32767);
rc sys_refcursor;
begin
select 'select 1 as row_type, ' ||
listagg(''''||rc_column_name||''' as col'||trim(to_char(rc_seq)), ',') within group (order by rc_seq)
||' from dual'
||' union all
select 2 as row_type, r1.*
from report1 r1
order by 1, 2'
into stmt
from dyna_report_columns ;
dbms_output.put_line(stmt);
open rc for stmt;
return rc;
end;
/
There is an additional column row_type which is referenced in the ORDER BY clause to guarantee that the header row is the first row of the result set.
Here is an Oracle LiveSQL Demo. Sorry, you need a free Oracle account to run this, but SQL Fiddle is a bit flaky at the moment.
Here is a SQL Fiddle version of the demo but Fiddle doesn't support DBMS_OUTPUT.
listagg(''''||rc_column_name||'''', ',')COL1 | COL2 | COL3 currently its the result set picking up the columns as User Id | Full Name | Contact Number , preferably the names used in REPORT1 would be betterrc_seq i think it would give me the same result which i expect listagg(''''||rc_column_name||''' as col'||trim(to_char(rc_seq)), ',')
dyna_report_columnstable have a column for the position of each column name?select drc.rc_seq,drc.rc_column_name from dyna_report_columns drc;