Select data from tables when needed columns are stored as records in a different table

Question

An app is developed where a user picks what data he wants to see in a report. Having data as

ReportDataValues

ID	TableName	ColumnName
1	customer	first_name
2	address	zip_code

Customer

ID	first_name	last_name	address_id
1	joe	powell	1
2	andy	smith	2

Address

ID	street	zip_code
1	main ave.	48521
2	central str.	56851

is it possible using generic SQL mechanisms (PIVOT, UNPIVOT or other way) to select such data from only specified table.column pairs in DataValues table as rows so the query is compatible with SQL Server and Oracle and is not using dynamic execution of generated statements (like EXEC(query) or EXECUTE IMMEDIATE (query) ), so the result would be like

Col1	Col2
joe	48521
andy	56851

Later SQL statement will be used in a SAP Crystal Reports reporting engine.

Answer 1

In Oracle, join the customer and address tables to every row of reportdatavalues and then use a CASE expression to correlate the expected value with the table columns and pivot:

SELECT col1, col2
FROM   (
  SELECT c.id,
         r.id AS value_id,
         CASE
         WHEN r.tablename = 'customer' AND r.columnname = 'id'
         THEN TO_CHAR(c.id)
         WHEN r.tablename = 'customer' AND r.columnname = 'first_name'
         THEN c.first_name
         WHEN r.tablename = 'customer' AND r.columnname = 'last_name'
         THEN c.last_name
         WHEN r.tablename = 'address' AND r.columnname = 'street'
         THEN a.street
         WHEN r.tablename = 'address' AND r.columnname = 'zip_code'
         THEN TO_CHAR(a.zip_code)
         END AS value
  FROM   customer c
         INNER JOIN address a
         ON a.id = c.address_id
         CROSS JOIN ReportDataValues r
)
PIVOT (
  MAX(value) FOR value_id IN (1 AS col1, 2 AS col2)
)

Which, for the sample data:

CREATE TABLE ReportDataValues (ID, TableName, ColumnName) AS
SELECT 1, 'customer', 'first_name' FROM DUAL UNION ALL
SELECT 2, 'address', 'zip_code' FROM DUAL;

CREATE TABLE Customer (ID, first_name, last_name, address_id) AS
SELECT 1, 'joe',  'powell', 1 FROM DUAL UNION ALL
SELECT 2, 'andy', 'smith',  2 FROM DUAL;

CREATE TABLE Address (ID, street, zip_code) AS
SELECT 1, 'main ave.',    48521 FROM DUAL UNION ALL
SELECT 2, 'central str.', 56851 FROM DUAL;

Outputs:

COL1	COL2
joe	48521
andy	56851

fiddle