How to retrieve data from table using its key?

Is it possible to write an SQL to retrieve column names from a table using the data that exists in the columns? If yes, how? If not, is Oracle doing anything about it?

• I have a set of data that exists in some of the columns of a table in an Oracle database. Using SQL, I would like to retrieve the column names that have the data. For eg, I have the names Ram and Sam (the data). With these in hand, I'd like to retrieve all the columns in a table that have these data in them.

• Answer:

  Assuming Oracle database, something like this will work: SET SERVEROUTPUT ON DECLARE   TYPE COLUMNS_T IS TABLE OF VARCHAR2(30);   T_COLUMNS COLUMNS_T;     found_ind INT; BEGIN   SELECT COLUMN_NAME   BULK COLLECT INTO T_COLUMNS   FROM all_tab_cols   WHERE TABLE_NAME = UPPER('dim_tenant')   AND OWNER = UPPER('SLALOMBNB')   AND DATA_TYPE = 'VARCHAR2';     FOR I IN T_COLUMNS.FIRST .. T_COLUMNS.LAST LOOP     found_ind := '0';         EXECUTE IMMEDIATE '     SELECT SUM(1)     FROM dim_tenant     WHERE ' || T_COLUMNS(I) || ' = ''Philip''' INTO found_ind;         IF found_ind >= 1 THEN       DBMS_OUTPUT.PUT_LINE(T_COLUMNS(I));     END IF;   END LOOP; END; Notes: If the data you're looking for exists in columns other than VARCHAR2 data type, you'll need to adjust the query from all_tab_cols accordingly.  If you have number data types you're looking for, you'll probably need a separate object and query for numeric values. This sounds like a very weird business scenario.  There's probably serious data modeling problems if you don't know the name of the column the data you're looking for exists in. Obviously, replace my table of dim_tenant and owner SLALOMBNB with your own table_name and schema name. Obviously, replace "Philip" with the text you're looking for. Output goes to DBMS_OUTPUT.  Make sure you run that first statement above DECLARE to get the output. This query basically just loops through all the column names and applies a where clause to a simple select statement that selects a dummy column.  It is horrifically inefficient but I can't think of a better way to do it off the top of my head.  So be warned might run super slow on large data sets.