Oracle dynamic query (return or select) result as table

Question

How can I show the result of running a dynamic query as a table in the output?

I want to show the result of the following query as a table in the output.

my table :

create table myTable(ROW_NAME varchar(10),COLUMN_NAME varchar(10),COLUMN_NAME_VALUE varchar(10));

table data :

insert into myTable (ROW_NAME,COLUMN_NAME,COLUMN_NAME_VALUE)
select 'ROW1','COL1','R1C1' from dual
union all select 'ROW1','COL2','R1C2' from dual
union all select 'ROW1','COL3','R1C3' from dual
union all select 'ROW2','COL1','R2C1' from dual
union all select 'ROW2','COL2','R2C2' from dual
union all select 'ROW2','COL3','R2C3' from dual
union all select 'ROW3','COL1','R3C1' from dual
union all select 'ROW3','COL2','R3C3' from dual
union all select 'ROW3','COL3','R3C3' from dual

my dynamic query :

DECLARE
   mycols VARCHAR2(1000);
   sqlCommand varchar2(1000);
   TYPE PivotCurTyp IS REF CURSOR;
   pivot_cv   PivotCurTyp;
   type pivotted is record (row_name myTable.row_name%type, col1 myTable.column_name_value%type, col2 myTable.column_name_value%type, col3 myTable.column_name_value%type);
   piv_rec  pivotted;
BEGIN
    select (select LISTAGG('''' || COLUMN_NAME || '''', ',') from myTable group by ROW_NAME FETCH FIRST 1 ROWS ONLY) into mycols from dual;
    select Concat('select * from myTable pivot ( max (COLUMN_NAME_VALUE) for COLUMN_NAME in (',Concat(mycols,')) ORDER BY ROW_NAME')) into sqlCommand from dual;
  DBMS_OUTPUT.PUT_LINE(sqlCommand);
   OPEN pivot_cv FOR sqlCommand;
   LOOP
     FETCH pivot_cv INTO piv_rec;
     EXIT WHEN pivot_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('ROW_NAME: ' || piv_rec.ROW_NAME || ' COL1: ' ||
         piv_rec.COL1 || ' COL2: ' || piv_rec.COL2 || ' COL3: ' || piv_rec.COL3);
   END LOOP;
   CLOSE pivot_cv;
END;
/

demo in db<>fiddle

Thanks for any help

You might be able to do something like this using a polymorphic table function (18c+). It's not straightforward though. — William Robertson, Commented Jul 21, 2021 at 22:09
@WilliamRobertson SQL Server also easily generates code, meaning there is no simple solution — persian-theme, Commented Jul 21, 2021 at 22:21
I don't follow. Why does that mean there is no simple solution? — William Robertson, Commented Jul 21, 2021 at 22:27
I'm not familiar with Oracle, and by referring to the link you provided, I said and panicked that there was no simple solution. — persian-theme, Commented Jul 21, 2021 at 22:33
Do you actually want to print the output in one column, or are you looking for a dynamic number of columns? If you need a dynamic number of columns, maybe this project can help? — Jon Heller, Commented Jul 23, 2021 at 0:32

nayi224 · Accepted Answer · 2021-07-22 03:11:38Z

1

Maybe I misunderstood, I guess what you want is this?

select 'ROW_NAME ' || t1.row_name || ' ' || listagg(t1.column_name || ': ' || t1.column_name_value, ' ') 
       within group(order by t1.column_name)
  from myTable t1
 group by t1.row_name
 order by t1.row_name

answered Jul 22, 2021 at 3:11

nayi224

5652 silver badges6 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Oracle dynamic query (return or select) result as table

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related