Bigquery select column only if not null

1
votes

I am an absolute beginner in Bigquery and SQL so apologies if this is a dumb question. I have a bigquery table like this

|Name|Value1|Value2|Value3|Value4|Value5|Value6|
|Ben |19    |45    |null  |19    |13    |null  |
|Bob |34    |null  |12    |null  |45    |43    |

My query only selects one row that matches the name in Name column. I want the result to only display columns that have non null values. For example if I do SELECT * FROM mytable WHERE Name = "Bob" I want the result to look like 

|Name|Value1|Value3|Value5|Value6|
|Bob |34    |12    |45    |43    |

Similarly, if I select for Ben I want the result to look like

|Name|Value1|Value2|Value4|Value5|
|Ben |19    |45    |19    |13    |

I have tried SELECT IF but don't seem to get the syntax right.

3
sqlgoogle-bigquery
You can't do that in standard SQL, because a SQL query must return a fixed list of columns (the database needs to know which columns the query will be returned by the query before executing it). - GMB
while this cannot be done - you most likely don't even need this to be done that way - so to help you - explain why you think you need such output? what is the real use-case? for sure it can be achieved easy but differently - Mikhail Berlyant
What if you select both Ben and Bob? - Scratte
Quote from Question - "My query only selects one row that matches the [given] name in Name column" - Mikhail Berlyant
Then it may be possible with 3 selects. First one selects the values into an array, the second select UNNEST(array from first select). The third re-flattens the result and uses a where not null. - Scratte

3 Answers

0
votes

I doubt if it can be achieved, because any SQL statement will act on record(s),i.e various columns, so if a column is null, it will affect all columns in the record that are to be retrieved. SQL STATEMENTS RETRIEVE ROWS(COLUMNS REFERENCED)

0
votes

You can not do that dynamically in SQL. If you need a query like that you could create it manually but it depends on the results you want to achieve.

In the case you showed for example, the query below would work but you would lose the table's header reference. 

SELECT value1,value2,value4,value5 FROM mytable WHERE value3 IS NULL AND value6 is NULL
UNION ALL
SELECT value1,value3,value5,value6 FROM mytable WHERE value2 IS NULL AND value4 is NULL

In this example it's possible to see that this kind of query is complicated to build if you have many conditions. Besides that, UNION ALL will always need the same number of columns in each separate query to work. If you need to create a generic query to do that, it's not gonna be possible.

I hope it helps

0
votes

You cannot select a variable amount of columns, but you may be able to create a SQL, with a combination of aggregate/pivot functions. You may be spending more time than it's worth trying to do it. I spend about two hours on the documentation, and I still feel almost clueless (If doesn't help that I don't have an account there, and my own database does not have the same exact functions).

See Google's BigQuery Documentation for examples.

I think you may be able to do it with UNNEST() and ARRAY(), but you'll lose the original column header information in the process.