1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.