Need to fetch not null values oracle query-CodePudding

Table structure looks like this

Table T1

Col1	COl2	Col3
null	A	null
B	null	null
null	null	C
D	null	E

Want to fetch A,B,C,D,E (Not null values)

CodePudding user response：

You can use union all:

select col1 from T1 where col1 is not null
union all
select col2 from T1 where col2 is not null
union all
select col3 from T1 where col3 is not null

CodePudding user response：

You can use:

SELECT value
FROM   t1
UNPIVOT (value FOR key IN (col1, col2, col3))

Which, for the sample data:

CREATE TABLE t1 (Col1, COl2, Col3) AS
SELECT NULL, 'A', NULL FROM DUAL UNION ALL
SELECT 'B', NULL, NULL FROM DUAL UNION ALL
SELECT NULL, NULL, 'C' FROM DUAL UNION ALL
SELECT 'D', NULL, 'E'  FROM DUAL;

Outputs:

VALUE

A

B

C

D

E

VALUE
A
B
C
D
E

db<>fiddle here