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?
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;
