Selecting all columns with NOT NULL values for given row in PostgreSQL-CodePudding

I have a table with id, A, B, C, D columns. Each row has a unique NOT NULL values of A, B, C, D columns: for the row with id=1 it is: A,C,D; for the row with id = 2 it is: B,C, and so on. I need help in writing SQL script that will return a list of columns with NOT NULL values for each given row. Something like:

SELECT NOT_NULL_Columns FROM table WHERE id=1;

The expected result is a table with only one row, names of columns and corresponding (not null) values

CodePudding user response：

It's not possible for a SQL query to return a variable number of columns, so I'll assume you want the names of the columns that have non-null values.

You can do:

select *,
  case when a is not null then 'A ' else '' end ||
  case when b is not null then 'B ' else '' end ||
  case when c is not null then 'C ' else '' end ||
  case when d is not null then 'D ' else '' end as not_null_columns
from t

Result:

 id  a     b     c  d     not_null_columns 
 --- ----- ----- -- ----- ---------------- 
 1   7     null  7  7     A C D            
 2   null  7     7  null  B C              
 3   null  7     7  7     B C D

See running example at DB Fiddle.

Now, if you wanted the non-null values that would be a different story. Since SQL cannot produce a variable number of columns, the solution could go in the direction of assembling all non-null values into a structure such as an array, an XML doc, a JSON doc, or even a string.

CodePudding user response：

You can convert the row to a JSON value and remove all keys (=columns) with NULL values:

select jsonb_strip_nulls(to_jsonb(t) - 'id')
from the_table t
where id = 1;