Sql-server – Build dynamic text search on table columns

dynamic-sqlsql servert-sql

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.

Best Answer

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)
        );
Related Question