I have a table with 100 columns.
It happens that I want the query to show only the columns that have "value <> 0"
So the column that has val = 0 should not be displayed.
Can anyone help with this
2
-
4Please show us what you've tried. Some code and desired results would be nice so we can help you.hanleyhansen– hanleyhansen2014-02-20 16:25:06 +00:00Commented Feb 20, 2014 at 16:25
-
Do you know the column names? Do you have them in a list or somewhere?Akshat Singhal– Akshat Singhal2014-02-20 16:36:58 +00:00Commented Feb 20, 2014 at 16:36
Add a comment
|
2 Answers
You can have a query like the following:
SELECT FLOOR(SUM(IF(column1 IN (0),0,1))/COUNT(*)),FLOOR(SUM(IF(column2 IN (0),0,1))/COUNT(*)),...,FLOOR(SUM(IF(column100 IN (0),0,1))/COUNT(*)) FROM table_name
This will give you a row with column value = 0 if the column contains 0 and 1 if column contains no 0.
Comments
You cannot adjust the number of columns returned. That is defined by a projection (your SELECT block) and a projection is evaluated against the table schema. It is expected that you know the number of columns and the types that you want returned in a projection.
As the previous user had mentioned, you can use a function or case statement to change the value in the column, or alternatively, you can restrict the rows returned with the WHERE clause (if some value or values are <> 0, for example).
The schema of the generated projection table returned must be static though.
