Build dynamic text search on table columns

Question

With no success, I am trying to build a dynamic search text on a table with two columns (Id & Number, both columns are unique).

The Number column should be grouped and start a new section of search (with OR). The Id column should be separated with "," (with IN).

Example (Please ignore the namings, I simplified my case and changed the names):

CREATE TABLE #MyTable
(
 Id INT,
 Number INT
);
INSERT #MyTable VALUES (6, 3), (7, 3), (8, 3), (16,15), (20, 15);

Expected result: @Query VARCHAR that will contain this:

' 
 SELECT Id, Number
 FROM #MyTable
 WHERE (
 Number = 3
 AND Id IN (6, 7, 8)
 )
 OR
 (
 Number = 15
 AND Id IN (16, 20)
 )
'

Note: The reason I am doing it dynamically because I do a dynamic filters search.

I guess the solution should use FOR XML PATH('') and STUFF() methods and probably also a GROUP BY, but I am not managing to do it (I succeeded to build a dynamic WHERE on one column but facing lots of difficulties in doing it with 2 columns. Actually, I can build it with a loop but I guess it could be done much better and therefore I ask you for a help.

Thanks in advance.

Answer 1

One way to do this using a set-based approach:

IF OBJECT_ID(N'tempdb..#MyTable', N'U') IS NOT NULL
DROP TABLE #MyTable;
CREATE TABLE #MyTable
(
 Id INT,
 Number INT
);
INSERT #MyTable VALUES (6, 3), (7, 3), (8, 3), (16,15), (20, 15);
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE ' OR ' END + N'(
 Number = ' + CONVERT(nvarchar(max), mt.Number) + N' 
 AND Id IN (' + STUFF((SELECT ', ' + CONVERT(nvarchar(max), mt1.Id) FROM #MyTable mt1 WHERE mt1.Number = mt.Number FOR XML PATH ('')), 1, 2, '') + N')
 )'
FROM #MyTable mt
GROUP BY mt.Number
SET @cmd = N'SELECT Id, Number
FROM #MyTable
WHERE ' + @cmd + ';
';
PRINT @cmd;

The results:

SELECT Id, Number
 FROM #MyTable
 WHERE (
 Number = 3 
 AND Id IN (6, 7, 8)
 ) OR (
 Number = 15 
 AND Id IN (16, 20)
 );