The SQL Query to find a value in multiple columns is really simple.
Just specify the column names and search expression and Ta Da!
Practice data
Create table Tbl1
(col1 varchar(50) null,
col2 varchar(50) null,
col3 varchar(50) null,
col4 varchar(50) null,
col5 varchar(50) null);
INSERT into Tbl1 VALUES
('Demo scheduled', 'Completed demo', 'Training postponed', 'Cancelled', 'Demo planned')
,('','Completed training','Demo postponed','Free day', 'Cancelled');
SQL Query -
Select * from Tbl1
WHERE 'Cancelled' in (col1, col2, col3, col4, col5);
The challenge arises if we need to pass the column names dynamically.
Step 1 is to derive the column names using the Information Schema table.
Select column_name from INFORMATION_SCHEMA.COLUMNS
Where Table_name = 'Tbl1'
Step 2 is to concatenate the column names derived in Step 1 above and separate them with a comma.
Select * from Tbl1
WHERE 'Cancelled' in (Select STRING_AGG(column_name, ',') from INFORMATION_SCHEMA.COLUMNS
Where Table_name = 'Tbl1');
Step 3 is to construct a dynamic SQL Query to pass these column names. If we directly try to pass the column names from the STRING_AGG function, the concatenated column names are treated as a single piece of string ('col1, col2, col3, col4, col5') and hence nothing results from the output.
We need (col1, col2, col3, col4, col5) and NOT ('col1, col2, col3, col4, col5') <-- Notice the quotes.
declare @col nvarchar(100)
declare @sql nvarchar(500)
Set @col = (Select STRING_AGG(column_name, ',') from INFORMATION_SCHEMA.COLUMNS
WHere Table_name = 'Tbl1')
Set @sql = 'Select * from Tbl1 WHERE ''Cancelled'' in (' + @col + ')'
exec(@sql)
NOTE - The quotes in 'Cancelled' are escaped using another quote.