I have got a bit of SQL which joins an unpivoted table with another dummy data table.
WITH tbl_job_data AS (SELECT 'N' argument1, 'Y' argument2, NULL argument3, 'Y' argument4 FROM DUAL)
, tbl_params AS (SELECT 1 col_seq, 'From Project Number' col_prompt, NULL col_data, NULL col_attrib FROM DUAL UNION ALL
SELECT 2 col_seq, 'To Project Number' col_prompt, NULL col_data, NULL col_attrib FROM DUAL UNION ALL
SELECT 3 col_seq, 'Through Date' col_prompt, NULL col_data, NULL col_attrib FROM DUAL UNION ALL
SELECT 4 col_seq, 'Summarize Cost' col_prompt, NULL col_data, NULL col_attrib FROM DUAL)
SELECT NULL AS col_seq
, NULL AS col_prompt
, d.col_data
, d.col_attrib
FROM tbl_job_data
UNPIVOT INCLUDE NULLS
(col_attrib
FOR col_data IN (argument1
, argument2
, argument3
, argument4)
) d
UNION ALL
SELECT tbl_params.col_seq
, tbl_params.col_prompt
, tbl_params.col_data
, tbl_params.col_attrib
FROM tbl_params;
COL_SEQ COL_PROMPT COL_DATA COL_ATTRIB
---------- ------------------- --------- ----------
ARGUMENT1 N
ARGUMENT2 Y
ARGUMENT3
ARGUMENT4 Y
1 From Project Number
2 To Project Number
3 Through Date
4 Summarize Cost
8 rows selected.
Sorry for being very naive and unlearned, but is there any way I can get the output into 4 lines, like this, by doing a union on the 2 statements:
COL_SEQ COL_PROMPT COL_DATA COL_ATTRIB
---------- ------------------- --------- ----------
1 From Project Number ARGUMENT1 N
2 To Project Number ARGUMENT2 Y
3 Through Date ARGUMENT3
4 Summarize Cost ARGUMENT4 Y
I am guessing not, because the 2 tables don't really have much in common with eachother, but I thought I'd ask.
Thanks
Previously helped on these queries by Frank Kulash
https://community.oracle.com/thread/3810284 and https://community.oracle.com/message/13361473