writing two Non Null values in two rows-CodePudding

I looking for efficient way to split the values in a row into two when there is non null values in column A and column B

CodePudding user response：

Here's one option; union two sets, return only those whose cola is not empty.

Sample data:

SQL> with test (cola, colb, colc) as
  2    (select 1, null, 'AA' from dual union all
  3     select 2,    6, 'BB' from dual union all
  4     select 3, null, 'CC' from dual union all
  5     select 4, null, 'DD' from dual union all
  6     select 5, null, 'EE' from dual
  7    ),

Query begins here:

  8  temp as
  9    (select cola, colc from test
 10     union all
 11     select colb, colc from test
 12    )
 13  select cola, colc
 14  from temp
 15  where cola is not null
 16  order by colc, cola;

      COLA COLC
---------- ----------
         1 AA
         2 BB
         6 BB
         3 CC
         4 DD
         5 EE

6 rows selected.

SQL>

You commented that the "input" is result of many joins and nested queries; no problem - just use that complicated query as a CTE:

with test as
  (your complicated query with joins and nested queries goes here),
temp as 
   the rest is copy/paste from above

CodePudding user response：

You can UNPIVOT:

SELECT output, col3
FROM   table_name
UNPIVOT (
  output FOR key IN (cola, colb)
)

Or, replace table_name with () brackets containing your query. Which, for the sample data:

Which, for the sample data:

CREATE TABLE table_name (cola, colb, col3) AS
SELECT LEVEL, CASE LEVEL WHEN 2 THEN 6 END, CHR(64 LEVEL) || CHR(64   LEVEL)
FROM   DUAL
CONNECT BY LEVEL <= 5;

Outputs:

OUTPUT COL3

1 AA

2 BB

6 BB

3 CC

4 DD

5 EE

OUTPUT	COL3
1	AA
2	BB
6	BB
3	CC
4	DD
5	EE

db<>fiddle here