Thanks to help from Frank Kulash yesterday (https://community.oracle.com/thread/3810284)
I can now use UNPIVOT to start out with this:
with tbl_data AS
(select 'N' argument1, 'Y' argument2, NULL argument3, 'Y' argument4 from dual)
select * from tbl_data;
ARGUMENT1 ARGUMENT2 ARGUMENT3 ARGUMENT4
--------- --------- --------- ---------
N Y Y
And end up converting a single row output to 4 rows and 2 columns via:
with tbl_data AS
(select 'N' argument1, 'Y' argument2, NULL argument3, 'Y' argument4 from dual)
SELECT *
FROM tbl_data
UNPIVOT INCLUDE NULLS
( col_heading
FOR col_name IN ( argument1
, argument2
, argument3
, argument4
)
);
COL_NAME COL_HEADING
--------- -----------
ARGUMENT1 N
ARGUMENT2 Y
ARGUMENT3
ARGUMENT4 Y
I wondered if there is any way Frank's solution can be changed to include 2 columns (e.g. COL_SEQ and COL_PROMPT) with hard coded values in them, e.g. "1" for the SEQ and "TEST" for the PROMPT, as I would like to do a UNION of Frank's solution with another query which returns 4 columns.
i.e. return:
COL_NAME COL_HEADING COL_SEQ COL_PROMPT
--------- ----------- ----------- -----------
ARGUMENT1 N 1 TEST
ARGUMENT2 Y 1 TEST
ARGUMENT3 1 TEST
ARGUMENT4 Y 1 TEST
Thanks