Is there a way to look for unordered values ​that change position?

Question

There is a table with 50 columns and they all contain numbers from 1 to 99. If I wanted to search, e.g. 8,36,52 no matter where they are, as long as they are in the same tuple, would I do it? I'm stuck because if I specify the column, I limit the search. Please, any help is welcome!

Answer 1

This will dynamically UNPIVOT the row without having to use Dynamic SQL

Example or dbFiddle

Declare @Search varchar(50)='8,36,52';

with cte as (
 Select Distinct Value From string_split(@Search,',')
) 
Select A.* 
 From YourTable A
 Cross Apply (
                Select Hits =Count(Distinct B1.Value)
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) B1
                 Join cte B2 on B1.Value=B2.Value
             ) B
 Where Hits = ( select count(*) from cte )

Answer 2

Here is another method based on XML/XQuery.

It is using XQuery's Quantified Expressions.

Quantified Expressions (XQuery)

Given two sequences, if every item in the first sequence has a match in the second sequence, the returned value is True. Exactly what we need.

The XML inside the CTE looks like this for each row:

<root>
  <source>
    <r>8</r>
    <r>36</r>
    <r>52</r>
  </source>
  <target>
    <ID>1</ID>
    <Col1>0</Col1>
    <Col2>15</Col2>
    <Col3>45</Col3>
    <Col4>7</Col4>
    <Col5>88</Col5>
    <Col6>22</Col6>
  </target>
</root>

SQL

-- DDL and data population, start
DECLARE @tbl TABLE
(
    ID INT IDENTITY PRIMARY KEY,
    [Col1] INT,
    [Col2] INT,
    [Col3] INT,
    [Col4] INT,
    [Col5] INT,
    [Col6] INT
);
INSERT INTO @tbl
VALUES
(0, 15, 45, 7, 88, 22),
(1, 36, 8, 7, 14, 52),
(32, 36, 36, 1, 1, 1); -- Duplicate Values
-- DDL and data population, end

DECLARE @Search VARCHAR(50) = '8,36,52'
    , @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT * 
    , TRY_CAST('<root>' + 
       '<source><r><![CDATA[' + REPLACE(@Search, @separator, ']]></r><r><![CDATA[') + 
          ']]></r></source>' + 
       (SELECT * FROM @tbl AS c 
       WHERE p.ID = c.ID
       FOR XML PATH(''), ROOT('target')) + 
       '</root>' AS XML)
          .query('every $x in /root/source/r/text()
                satisfies ($x = (/root/target/*[local-name()!="ID"]/text()))')
          .value('text()[1]', 'bit') result
    FROM @tbl AS p
)
SELECT * FROM rs
WHERE rs.result = 1;