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, Thanksmysqlsql
For this particular case, you can do:
where greatest(col1, col2, col3) = 'completed' and least(col4, col5, col6) = 'incompleted'
The use of
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.
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.