AnswerBun.com

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

Stack Overflow Asked by Vini on September 23, 2020

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.

2 Answers

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.

Correct answer by Koen Lostrie on September 23, 2020

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.

Answered by Jon Heller on September 23, 2020

Add your own answers!

Related Questions

Change null to 0 in response

1  Asked on December 3, 2021 by edwin-landsfield

   

Print complete SQL for all queries made by objection.js

2  Asked on December 3, 2021 by eugene-kim

 

Easiest way to convert a char* of hex to actual hex in C?

1  Asked on December 3, 2021 by purplespark

 

Xcode confused with IQAir Api Parsing

1  Asked on December 3, 2021 by unkowncoder

       

gRPC server cannot be built and throws error

0  Asked on December 3, 2021 by jibo_libin

   

How to correctly map huge json file to Java (pojo)?

1  Asked on December 3, 2021 by orkhan-hasanli

     

check all items in csv column except one [python pandas]

2  Asked on December 2, 2021 by blindside044

       

Unpivot a single into two rows – T-SQL

1  Asked on December 2, 2021 by error-1004

       

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir