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.

for that you will need dynamic sql, please tag the database system you are using — nbk, Jan 13 '23 at 12:28
@nbk as noted in the question it should work both with SQL Server and Oracle. — We8, Jan 13 '23 at 12:29
@nbk is it possible to have dynamic sql that would execute without error for SQL Server and Oracle ? — We8, Jan 13 '23 at 12:32
no as far as i know, you need a wrapper, as both database have different syntax — nbk, Jan 13 '23 at 12:38
tag both databases, show your code so far, and change the title as it has litte to do which your essential question — nbk, Jan 13 '23 at 12:56
It is strange you have a database wth a table where you store table and column names, as these belong to the database structure and should not be data. However, "dynamic SQL" only means you select the data from your table DataValues, then you use the result to build a query string for the final query, and then you run that built query to get the result you are after. Many DBMS offer features to do this inside the DBMS, but you can just as well do this from outside with an app written in Java, C#, VisuaBasic, whatever. — Thorsten Kettner, Jan 13 '23 at 14:05
You may also want to select data from system tables, because somehow you must know how to join the tables mentioned in DataValues. This would mean running through the foreign key hierarchy, which is not easy. Or is it always the same two tables? — Thorsten Kettner, Jan 13 '23 at 14:08
@ThorstenKettner user picks the data pieces user wants to see in a report. — We8, Jan 13 '23 at 14:26
Am I missing something? Aren't you simply joining Customer to Address on Customer.Address ID = Address.ID? Also, what is the front end? The field names should be static, so you could keep them in a table, populate a multi-select dropdown with the fields available, and then loop through the dropdown with a SELECT statement to keep the fields you want. from the query I described in the first sentence. — Johnny Bones, Jan 13 '23 at 14:57
@JohnnyBones front-end is a web app. Input from a user is stored in ReportDataValues table, meaning only those table.columns should be output in a report. — We8, Jan 13 '23 at 15:07
@We7 - You can definitely make a multi-select in C#, and read the values that are selected into a string, call it "X". Then, SELECT & X & FROM (Select A.*, B.* From Customer A left join Address B on A.Address ID = B.ID). — Johnny Bones, Jan 13 '23 at 15:17
@JohnnyBones that would be an option if statement was being executed under .NET but it is SAP Crystal Reports using the SQL query. — We8, Jan 13 '23 at 15:32

score 0 · Answer 1 · answered Jan 13 '23 at 14:41

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

for the sake of simplicity, provided tables are simplified as real ones have 50+ columns and may change over time meaning new column would need a change in the query. — We8, Jan 13 '23 at 15:03
@We7 You are not going to find a simple solution; you either hard-code the table/column names into the query (and need to update the query if you change the tables) or you use dynamic SQL (and potentially run the risk of introducing SQL injection vulnerabilities). You need to pick whichever method is best for **YOUR** business case. — MT0, Jan 13 '23 at 15:42
Chosen solution is to create views for each of DBMS and later use it in query with pivot. — We8, Jan 15 '23 at 20:46

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

1 Answers1