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

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

Christiano
1#
Christiano Published in 2018-02-14 19:20:27Z
 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? select [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
Gordon Linoff
2#
Gordon Linoff Reply to 2018-02-14 19:31:40Z
 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.
clinomaniac
3#
 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''); EXECUTE @s  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.