Query a Table and a Column Name Stored in a Table

Query a table and a column name stored in a table

Using Peter M's query to build the SQL text, and then harnessing the dark power of XML:

create table attributes (id, entity_id, table_name, column_name)
as
select 1, 3, 'VALUES_A', 'VALUE_1' from dual union all
select 2, 2, 'VALUES_B', 'VALUE_3' from dual union all
select 3, 2, 'VALUES_A', 'VALUE_2' from dual;

create table values_a (entity_id, value_1, value_2, value_3)
as
select 1, 'Monday', 42, 'Green' from dual union all
select 2, 'Sunday', 3000, 'Blue' from dual union all
select 3, 'Wednesday', 1, 'Black' from dual;

create table values_b (entity_id, value_1, value_2, value_3)
as
select 1, 'Tuesday', 26, 'Green' from dual union all
select 2, 'Saturday', 3, 'Red' from dual union all
select 3, 'Wednesday', 15, 'White' from dual;

Query:

with queries as
     ( select table_name, column_name, entity_id
            , 'select '|| column_name || ' as c from ' || table_name ||
             ' where entity_id = ' || entity_id ||
              case
                  when id = max_id then ''
                  else ' union all '
              end as sqltext
       from 
           ( select a.*, max(a.id) over (order by id) max_id from attributes a ) )
select table_name, column_name, entity_id
     , extractvalue(xmltype(dbms_xmlgen.getxml(sqltext)),'/ROWSET/ROW/C') as sql_result
from   queries;

Results:

TABLE_NAME COLUMN_NAME  ENTITY_ID SQL_RESULT
---------- ----------- ---------- ---------------------------------------------------
VALUES_A   VALUE_1              3 Wednesday
VALUES_B   VALUE_3              2 Red
VALUES_A   VALUE_2              2 3000

How to retrieve a column name from a table that is stored as a value in another table

i add the [Last Refreshed] column to my tables and write this query and give me the correct answer

DROP TABLE  IF EXISTS #DB_DUMMY

CREATE TABLE  #DB_DUMMY (
[TABLENAME] VARCHAR(512),
[LAST_REFRESHED] VARCHAR(533)
);

DECLARE @COMMAND NVARCHAR(MAX)

SELECT @COMMAND = STRING_AGG(' INSERT INTO #DB_DUMMY SELECT DISTINCT '+CHAR(39)+T.name+CHAR(39)+',['+C.name+'] FROM '+S.name+'.'+T.name + ' GO', CHAR(13)+CHAR(10))
FROM sys.all_columns C
INNER JOIN sys.tables T ON C.object_id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE C.name = 'Last Refreshed'

PRINT(@COMMAND)

EXEC(@COMMAND)

SELECT * FROM #DB_DUMMY

two first line with IF EXISTS is new syntax in sql server 2017

How can I get column names from a table in SQL Server?

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

Get value based on column name stored in another table

You can use to_jsonb() to make a JSON for the row from "DocumentRegistryAttributes", with the column names as keys and then select the text from the JSON where the key is the text in "DocumentSubject"."List1Left".

SELECT *,
       to_jsonb(dra)->>ds."List1Left"
       FROM "DocumentSubject" ds
            LEFT JOIN "DocumentRegistryAttributes" dra
                      ON dra."DocumentSubjectId" = ds."Id";

How to use value from one table as column name in another table in SQL query

SQL requires all identifiers are fixed in your expressions at the time the query is parsed. But you could do a CASE expression like this:

SELECT
    F.date,
    F.flight_num,
    F.dep_apt_code as dep,
    F.arr_apt_code as arr,
    CASE F.dep_apt_code 
      WHEN 'ATL' THEN W.ATL 
      WHEN 'IAD' THEN W.IAD 
      WHEN 'JFK' THEN W.JFK
      WHEN 'SFO' THEN W.SFO 
    END AS dep_weather
FROM Flights AS F JOIN Weather AS W ON F.date = W.date;

The comment above says that you should normalize your structure. This means to store the weather per city on individual rows instead of in columns on one row.

USE [Database Name]
SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName'