I've found a nice way to check values in different columns in an SQL Server table and return only the columns where the search value is in.
In my example, we have a table with several columns (No1 - No7). We want to find out which columns have the value 8 in it and only return these ones.
Check the code:
First we create a table to check.
DECLARE @t TABLE (AdrNr INT, Nr1 INT, Nr2 INT, Nr3 INT, Nr4 INT, Nr5 INT, Nr6 INT, Nr7 INT)
Then we enter some values in it:
INSERT INTO @t
VALUES (500, 3, 4, 8, 42, 5, 76, 91)
This is the SQL code to return only the columns where the value "8" can be found:
SELECT AdrNr, a, b
FROM @t
CROSS APPLY (
VALUES
('Nr1', Nr1),
('Nr2', Nr2),
('Nr3', Nr3),
('Nr4', Nr4),
('Nr5', Nr5),
('Nr6', Nr6),
('Nr7', Nr7)
) t(a, b)
WHERE b = 8
