查询表和表中存储的列名

使用Peter M的查询来构建SQL文本，然后利用XML的强大功能：

create table attributes (id, entity_id, table_name, column_name)asselect 1, 3, 'VALUES_A', 'VALUE_1' from dual union allselect 2, 2, 'VALUES_B', 'VALUE_3' from dual union allselect 3, 2, 'VALUES_A', 'VALUE_2' from dual;create table values_a (entity_id, value_1, value_2, value_3)asselect 1, 'Monday', 42, 'Green' from dual union allselect 2, 'Sunday', 3000, 'Blue' from dual union allselect 3, 'Wednesday', 1, 'Black' from dual;create table values_b (entity_id, value_1, value_2, value_3)asselect 1, 'Tuesday', 26, 'Green' from dual union allselect 2, 'Saturday', 3, 'Red' from dual union allselect 3, 'Wednesday', 15, 'White' from dual;

询问：

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_resultfrom   queries;

结果：

TABLE_NAME COLUMN_NAME  ENTITY_ID SQL_RESULT---------- ----------- ---------- ---------------------------------------------------VALUES_A   VALUE_1   3 WednesdayVALUES_B   VALUE_3   2 RedVALUES_A   VALUE_2   2 3000