Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL to convert single row with 4 columns to 4 rows with 4 columns

969483Oct 21 2015 — edited Oct 21 2015

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

This post has been answered by Frank Kulash on Oct 21 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 18 2015
Added on Oct 21 2015
2 comments
298 views