1

I have a DB table has more than 50 columns.

I am querying this table, it should return only one row any time. as below image from sqldeveloper. enter image description here here I need to construct query/pl-sql block, to find out table column as a key and query result as values.

Eg: Key - Value

      TASK_EVENT_ID   - 1765

      EVENT_TYPE        - ASR_UPDATE

       ...... etc upto all columns in my table.

In my procedure I am constructing as like below.

 Message_handle RAW(64); 
Enqueue_options DBMS_AQ.enqueue_options_t;
Message_properties DBMS_AQ.message_properties_t;  msg
SYS.AQ$_JMS_BYTES_MESSAGE;
msg.set_string_property('TASK_EVENT_ID', v_TASK_EVENT_ID);
msg.set_string_property('EVENT_TYPE', v_EVENT_TYPE);

------------------------------------------------------------------------
0

1 Answer 1

1

According to your requirement you might be looking for UNPIVOT operator(If you are using 11g or later version). It converts column-based data into separate rows.
For example:

SQL> desc tbl1
 Name                      Null?    Type
 ----------------------------------------- -------- --------------
 ID                         NUMBER
 NAME                           VARCHAR2(20)

SQL> SELECT * FROM tbl1;

    ID NAME
---------- --------------------
     1 test name

WITH all_objects_data AS(
SELECT TO_CHAR(id) id, name FROM tbl1)
SELECT column_name, column_value FROM all_objects_data UNPIVOT(column_value FOR column_name IN (id,name));

COLU COLUMN_VALUE
---- ----------------------------------------
ID   1
NAME test name

For details: Pivot and Unpivot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.