Select only columns with values

Question

Select only columns with values

Asked 10 years, 1 month ago

Viewed 835 times

0

I have a table with a sequence of fields with almost the same name, changing only the final character, and I need to make a query that returns only the columns with some value, currently I do the following:

select pag, descricao,cod_vcto1,cod_vcto2,cod_vcto3,cod_vcto4,cod_vcto5 from tabela_exemplo where pag =2;

pag descricao   cod_vcto1   cod_vcto2   cod_vcto3   cod_vcto4   cod_vcto5
2   28 DIAS         28          31      null       null        null

I would like to return, only 28 DAYS 28/31. However I have 15 columns starting with cod_vcto, which vary from cod_vcto1 to cod_vcto15, there is some way to select only the columns that are not null?

2

Thiago, in your query you cannot "guess" which column will be empty and remove it from your select. In addition, if possible, review the structure of your table. Instead of 15 cod_vcto, just a single column repeating the id and description. What I suggested is called normalization and more details you can find in this reply

– gmsantos

2014/10/20 at 17:46
@Thiago, It seems really that your schema was poorly normalized and lacks a table of relationship between salaries and the table_example. Nevertheless you can make use of COALESCE to return something in place of null. As to return dynamically different columns, it gives p/ do horrible gambiarras with dynamic queries but I strongly advise not to do this

– jean

2014/10/21 at 19:03

2 answers

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.

by Motta • **3,568** points · Answer 1 · 2014-10-20T21:50:37+00:00

As gmsantos suggested the clean solution is to normalize , but a dirty (and much) solution is simply to concatenate (converting the string) the columns the result will be only the columns with value, there would be no header to indicate "which".

But I recommend a normalization unless there is a good reason for the current model.

by Kaique Mota • **496** points · Answer 2 · 2014-10-20T17:47:14+00:00

I don’t know any way to make a select that returns only the columns that are not null, however if you are using some programming language to be doing the application, you can create an object class and make a list with the objects, and thus picking up only the desired columns!

hope I’ve helped!