I have a SQL Server database with a table with 20 columns. These columns have data as agree or disagree. Now I want to show rows in these columns which have "agree" data in them. I can use where clause but it is a time consuming task for 20 columns. I am looking for a SQL query which does this task.
-
In the time it took you to write this question I could have written out that query.MJH– MJH2018-05-20 10:37:32 +00:00Commented May 20, 2018 at 10:37
-
I can write like that its not an issue but its is not an efficient way to do things what if there are more than 20 columns this will be a time consuming task.ZAIN-UL ABDIN– ZAIN-UL ABDIN2018-05-20 10:40:06 +00:00Commented May 20, 2018 at 10:40
-
This is kinda amusing. Mostly when people ask a "Gimme Ze code!" question, a recurrent comment is "What have you done?". Just to see if the OP at least put some effort into it. But this time the whole point is "How not to put effort into this". But perhaps you should add a minimal example and expected results?LukStorms– LukStorms2018-05-20 10:49:56 +00:00Commented May 20, 2018 at 10:49
-
@ZAIN-ULABDIN . . . I don't understand what result you want, and only sort of get the data structure. You should show a simplified example in your question.Gordon Linoff– Gordon Linoff2018-05-20 11:06:39 +00:00Commented May 20, 2018 at 11:06
Add a comment
|
4 Answers
You can use in:
select t.*
from t
where 'agree' in (col1, col2, ... col20);
Comments
There is no shortcut for this type of scenarios, If you want to compare all the columns, you have to explicitly mention each and every column like .
WHERE Col1='agree' AND Col2="agree"....
To avoid coding, you may go with dynamic query creation or creating a function, but ultimately it will be executed as same query comparing all the columns.
Comments
What about JOIN ?
If u have such a complex logic, best practices advise to keep data in different tables.
Comments
Here's some simplified example code that tests out several methods to return records that may or may not agree.
Just for the fun of it actually.
declare @T table (id int identity(1,1) primary key, col1 varchar(30), col2 varchar(30), col3 varchar(30));
insert into @T (col1, col2, col3) values
('agree','agree','agree'),
('agree','disagree','disagree'),
('agree','disagree',null),
('disagree','disagree','disagree'),
('disagree','disagree',null),
(null,null,null);
select 'OR' as method, * from @T
where (col1='agree' OR col2='agree' OR col3='agree');
select 'AND' as method, * from @T
where (col1='agree' AND col2='agree' AND col3='agree');
select 'IN' as method, * from @T
where 'agree' IN (col1, col2, col3);
select 'NOT IN' as method, * from @T
where 'agree' NOT IN (col1, col2, col3);
select 'LIKE' as method, * from @T
where CONCAT('-',col1,'-',col2,'-',col3,'-') LIKE '%-agree-%';
select 'NOT LIKE' as method, * from @T
where CONCAT('-',col1,'-',col2,'-',col3,'-') NOT LIKE '%-agree-%';
select 'ALL' as method, * from @T
where 'agree' = ALL(select col from (values (col1),(col2),(col3))q(col));
select 'SOME' as method, * from @T
where 'agree' = SOME(select col from (values (col1),(col2),(col3))q(col));
select 'ANY' as method, * from @T
where 'agree' = ANY(select col from (values (col1),(col2),(col3))q(col));
select 'EXISTS' as method, * from @T
where EXISTS (
select 1
from (values (col1),(col2),(col3))q(col)
where col = 'agree'
);
select 'NOT EXISTS' as method, * from @T
where NOT EXISTS (
select 1
from (values (col1),(col2),(col3))q(col)
where col = 'agree'
);
