CODEWITHSUNDEEP
sqlplsqlplsqldeveloper
user2213892
user2213892

Reputation: 139

how to dynamically get column names and use them

I am using this code to output some table data.

select uID, ColumnName, ColumnResult
from TABLE
unpivot
(
ColumnResult
for ColumnName in (COL1,COL2,COL3)
)u

My problem is I have to type in every column and some of my tables have 100+ rows. This can be very tedious to write every column (Col1,Col2,Col3, etc). Is there a way to dynamically get all the column names and put them inside the 'IN'statement?

Upvotes: 0

Views: 127

Answers (1)

Taryn
Taryn

Reputation: 247880

You could create a procedure that generates the sql string that would then be executed. Here is a sample solution:

CREATE OR REPLACE procedure dynamic_unpivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select id, columnName, columnResult 
                                               from yourtable ';

    sql_unpiv varchar2(50) := null;

    begin
        for x in (select t.column_name ls 
                    from user_tab_columns t
                    where t.table_name = 'YOURTABLE'
                        and t.column_name not in ('ID'))
        loop
            sql_unpiv := sql_unpiv ||
                '  '||x.ls||' ,';

                dbms_output.put_line(sql_unpiv);
        end loop;

        sql_query := sql_query || 'unpivot
                                               (
                                                    columnResult
                                                    for columnName in ('||substr(sql_unpiv, 1, length(sql_unpiv)-1)||')
                                                )';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you could use the following to execute the result (my sample is from TOAD):

variable x refcursor
exec dynamic_unpivot(:x)
print x;

Upvotes: 1

Related Questions