Oracle SQL Query to change column headers on basis of conditions from another table

Question

EDIT: This post is related to Oracle and requirement is for sql expression to fulfill requirement below.
I have a requirement where there is table called data with columns:
DATA_ID
APP_ID
COLUMN_1
COLUMN_2
COLUMN_3

I have to write a query which replaces the Column header names with the ones in the table DATA_HEADER on the basis of app_id.
So if app_id=1, the columns would be :
COLUMN_1 :> Name
COLUMN_2 :> Status
COLUMN_3 :> Assigned

And if app_id=2, the columns would be :
COLUMN_1 :> Title 
COLUMN_2 :> Status
COLUMN_3 :> Title_New

There should be additional condition, if there is no name in data_header it would stick with the original column name, i.e. COLUMN_1,COLUMN_2,COLUMN_3 as in future according to app_ids there will be more/less column headers available as per app_id. SO if column header is present, it should replace, else remain original.
The 2 tables would be joined on basis of APP_ID.

Koen Lostrie · Accepted Answer

I created a sample app in your workspace, based on the sample data emp/dept (can be loaded via sql workshop > utilities > sample datasets). I added a table emp_header with some data. The idea is that you define a different column header for a database column per a different application
create table emp_header (
    id                             number generated by default on null as identity  
                                   constraint emp_header_id_pk primary key,
    app_id                         number,
    db_column_name                 varchar2(100) not null,
    report_header                  varchar2(100) not null
)
;

insert into emp_header (app_id, db_column_name, report_header ) values (1, 'ENAME', 'Employee Name');
insert into emp_header (app_id, db_column_name, report_header ) values (2, 'ENAME', 'Ename');
insert into emp_header (app_id, db_column_name, report_header ) values (1, 'JOB', 'Employee Job');
insert into emp_header (app_id, db_column_name, report_header ) values (2, 'JOB', 'Job');

On the home page I created a select list where a user can select an application. On select of the value, the page gets submitted an redirected to page 2.
In page 2 there are 2 page items: P2_COLUMN_1 and P2_COLUMN_2 with a computation before header to set the value when the page is loaded.
Example computation:
DECLARE
  l_returnvalue emp_header.report_header%TYPE;
BEGIN
  SELECT report_header 
    INTO l_returnvalue
    FROM emp_header WHERE db_column_name = 'ENAME' AND app_id = :P2_APP_ID;
  RETURN l_returnvalue;
EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN 'ENAME Original Value';
END;

As you can see in the computation code, there is a fallback value in case no value is found in the emp_header table. In this case it is 'ENAME Original Value', but you can make it whatever you want.
In the report attributes column attributes, the heading for column ENAME is set to &P2_COLUMN_1. and for column JOB to &P2_COLUMN_2.
Run page 1 and select a value for app id. See the different column headers based on your selection.

Jon Heller · Answer

SQL statements that return a variable result set require Oracle Data Cartridge, ANYDATASET, and other tricks. Instead of coding that yourself, you can use my open source program Method4. After installing it, you must write a query that generates the query, like this:
select * from table(method4.dynamic_query(
    q'[
        select
            'select data_id, app_id, ' ||
            'COLUMN_1 ' || max(case when id = 1 then '"'|| header_name || '"' else null end) || ', ' ||
            'COLUMN_2 ' || max(case when id = 2 then '"'|| header_name || '"' else null end) || ', ' ||
            'COLUMN_3 ' || max(case when id = 3 then '"'|| header_name || '"' else null end) ||
            ' from data where app_id = 2' sql_statement
        from data_header
        where app_id = 2
    ]'
));

DATA_ID   APP_ID   Title   Status     Title New
-------   ------   -----   --------   ---------
    100        2   Anna    Inactive   BA
    100        2   Ronnie  Active     MI and AI

Alternatively, if you're using 18c you could create a polymorphic table function. But as others have pointed out, this problem is usually best solved in an application like Apex.

Oracle SQL Query to change column headers on basis of conditions from another table

2 Answers

Add your own answers!

Ask a Question