PL/SQL Extract Column Names and use in select statment

Question

not sure if this is possible at all but im trying to do this with as little manual work as possible.

I have a table with 150 columns based on different combinations of factors. I wish to extract the column names where a certain certain string is inside the column name.

I have done the following which does this. This is a basic example of what I have

  --Create the table 
    Create Table temp
    (id number,
    Fac1_Fac2_Fac_3_Fac4_Fac5 number,
    Fac1_Fac6_Fac_3_Fac4_Fac5 number,
    Fac1_Fac6_Fac_7_Fac4_Fac5 number,
    Fac1_Fac9_Fac_3_Fac4_Fac5 number,
    Fac1_Fac10_Fac_3_Fac4_Fac5 number,
    Fac1_Fac2_Fac_3_Fac11_Fac5 number,
    Fac1_Fac2_Fac_3_Fac4_Fac12 number,
    Fac13_Fac2_Fac_3_Fac4_Fac5 number);

    Insert into temp Values (1,35634,3243,343,564,56,4635,3,334);
    Insert into temp Values (2,3434234,3243,343,564,56,435,3,34234);
    Insert into temp Values (3,5555,3243,33,564,56,435,3,3434);
    Insert into temp Values (4,34234,343,343,564,56,4335,3,34);
    commit;

    --Extract Column Names 
    Select * from (
                  Select COLUMN_NAME 
                  from user_tab_cols 
                  where lower(table_name) ='temp'
                  )
    where column_name like '%FAC13%'

    --This is what I want to automate.
    Select id, FAC13_FAC2_FAC_3_FAC4_FAC5 
    From temp

--I want the column name to come fron the select statment above as there may be lots of names. 

Basically, I want to select all the rows from my table that have Fac13 in the column name all in one query if possible.

Thanks

Answer 1

I do not think you can do that in one query. First, your extract column names query can be simplified to one query as a cursor, and then use a dynamic select statement as follows:

CREATE OR REPLACE proc_dyn_select IS
CURSOR c1 IS 
                  SELECT column_name 
                  FROM user_tab_cols 
                  WHERE LOWER(table_name) ='temp' and column_name LIKE '%FAC13%';

cols  c1%ROWTYPE;
sqlstmt  VARCHAR2(2000);

BEGIN
   OPEN c1;
     LOOP
         FETCH c1 into cols;
         EXIT WHEN c1%NOTFOUND;
         sqlstmt := sqlstmt ||cols.column_name||',';
      END LOOP;
   CLOSE c1;
   sqlstmt := 'select '||substr(sqlstmt, 1, length(sqlstmt)-1)||' FROM temp';
   EXECUTE IMMEDIATE sqlstmt;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error '||sqlerrm);
END;
/

Explanation

First, the cursor will store the columns that meet your conditions (to be from the table temp and the column names have the sub string FAC13. Then in execution section (after BEGIN), you will build your query dynamically using columns names stored in the cursor c1. With each round of the loop, a column name is added as a string and concatenated with a comma. So a string of columns will be built like this 'col1, col2, col3, ... coln,'. The string is stored in sqlstmt variable.

After the loop end, you amend the string to build sql statement, by adding the keywords SELECT, FROM and table name. However, we remove the last character of the sqlstmt variable, as it is an extra comma.

EXECUTE IMMEDIATE statement, will run the query stored in sqlstmt.

By using a procedure, you can always pass parameters, such that this procedure can perform any dynamic sql statement you want.