how to select not null values column dynamically?

Question

I created a table like below. I want to select only NOT NULL column dynamically like(NO, NAME,SAL_1). No need to select SAL,SAL_2.

Note: Initially I don't know the columns values.

create table sample(no integer,name varchar(20),sal integer,sal_1 integer,sal_2 integer);
insert into sample(name,sal_1) values('aaa',10);
insert into sample(no,name,sal_1) values(20,'',20);
insert into sample(sal_1) values(30);

select * from sample;

data like below

NO        NAME    SAL   SAL_1 SAL_2
20       (null)   (null)  20  (null)
(null)    (null)  (null)  30  (null)
(null)    aaa     (null)  10  (null)

Expected op:

NO       NAME   SAL_1
20      (null)  20
(null)  (null)  30
(null)  aaa     10

@Velu: So total no. of columns, i.e. 5 columns, in your table will be fixed, right ? — Keyur Panchal
– Keyur Panchal, Commented Jul 4, 2017 at 11:48
@KeyurPanchal ..No columns is not fixed.In my table have 30 columns for sample purpose i given only 5. — Velu
– Velu, Commented Jul 4, 2017 at 12:37

Fabian Pijcke · Accepted Answer · 2017-07-04 12:37:29Z

0

The following query generates the query you want to execute through some dynamic SQL procedure.

Unfortunately I don't know well the Oracle syntax, the following is in PostgreSQL, but I guess you'll only have to change string_agg with the pertinent Oracle function catenating strings.

WITH columns (c) AS (
  SELECT 'no' WHERE EXISTS (SELECT * FROM sample WHERE no IS NOT NULL)
  UNION
  SELECT 'name' WHERE EXISTS (SELECT * FROM sample WHERE name IS NOT NULL)
  UNION
  SELECT 'sal' WHERE EXISTS (SELECT * FROM sample WHERE sal IS NOT NULL)
  UNION
  SELECT 'sal_1' WHERE EXISTS (SELECT * FROM sample WHERE sal_1 IS NOT NULL)
  UNION
  SELECT 'sal_2' WHERE EXISTS (SELECT * FROM sample WHERE sal_2 IS NOT NULL)
)
SELECT 'SELECT ' || string_agg(c, ', ') || ' FROM sample;' FROM columns;

answered Jul 4, 2017 at 12:37

Fabian Pijcke

3,31027 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

G one · Accepted Answer · 2017-07-07 05:38:07Z

0

Try This,

I have checked and exactly match your output.

select no,"NAME",sal_1 from sample having count(sal_1)>0  group by 
sal_1,name,no order by name desc

edited Jul 7, 2017 at 5:38

G one

2,7392 gold badges17 silver badges18 bronze badges

answered Jul 7, 2017 at 5:32

Ashish4434

1186 bronze badges

Comments

Ashish4434 · Accepted Answer · 2017-07-05 04:59:54Z

-1

Try This,

select * from sample where nvl(NO,0)> 0 and nvl(length(NAME),0)>0 and nvl(SAL_1,0)>0

answered Jul 5, 2017 at 4:59

Ashish4434

1186 bronze badges

Collectives™ on Stack Overflow

how to select not null values column dynamically?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related