How to extract one specified condition for all columns in Where clause?

user9296546 Source

I have a table that have 200 columns and I want to select all columns that have 'completed' or 'incomplete' values. this table have 180 columns that can have 'completed' or 'incomplete' values and other columns are dates, signatures, ids, etc.

So The first idea is write a long query like ,

SELECT column2,column4,column5,...,column199 FROM table WHERE column2 like  'completed' or column2 like 'incomplete' or column4 like....

But this query is too simple and too long. Is there any way to write something like below?

[Columns that have values like 'completed' or 'incomplete'( if the value is not them do not select them)]
from Table 

Please someone help or any idea, Thanks



answered 6 months ago Gordon Linoff #1

For this particular case, you can do:

where greatest(col1, col2, col3) = 'completed' and
      least(col4, col5, col6) = 'incompleted'

The use of least() and greatest() is specifically because there are two values and is based on the ordering of the values.

I should note that the data structure is suspicious. You would be better served by having 180 rows for each of the statuses.

answered 6 months ago clinomaniac #2

This select query should get you all the columns in your table.

SELECT column_name 
FROM   information_schema.columns 
WHERE  table_schema = 'your_db' 
       AND table_name = 'your_table' 
ORDER  BY ordinal_position 

From the results, you can make a string that will concat the column names in whatever fashion you want. Then you'll end up with a string that will be generated and will have all the columns that you need and you wouldn't have to type all the column names individually. Then you can execute the string using the EXECUTE command. Here is a sample of how it would look like.

SET @s = CONCAT('SELECT * FROM your_table WHERE greatest(', SELECT GROUP_CONCAT(column_name)
    FROM   information_schema.columns 
    WHERE  table_schema = 'your_db' 
           AND table_name = 'your_table'
    GROUP BY table_name
    ORDER  BY ordinal_position, ') = ''completed''); 


Please keep in mind that the above example is not executable. The 's need to be adjusted properly to accommodate for string literals and such but this should be able to give you information about how to use dynamic sql to get the result that you need.

comments powered by Disqus